Split into different rows using SSJS or Query or whatever

Question

I'm totally lost spliting my dataset. The dataset looks like below.

What I want to is split 'Field 3' and build exact like under picture.

Is there any way to achieve this requirement? If so, please give me some direction...

Thanks in advance!

Answer 1

The common concept to do this is that you would need to split the "field 3" column values by , character and hence insert a new row to a new DE for each element.

The following code here is a sample code to do that in SSJS. It might need to be modified/enhanced to fit with your actual needs.

<script runat="server">
Platform.Load("Core","1.1.1");
var de1CustKey = "Your original DE customer key";

var prox = new Script.Util.WSProxy();
var cols = ["Field 1", "Field 2", "Field 3"];
var data = prox.retrieve("DataExtensionObject["+ de1CustKey +"]", cols);

var rowcount = data.Results.length;
if (rowcount > 0) {
    for (var i=0; i < rowcount; i++) {
        var field3Value = data.Results[i].Properties[2].Value;
        var elements = field3Value.split(",");
        if (elements.length > 0) {
            for (var n=0; n < elements.length; n++) {
                Platform.Function.InsertData("Your split DE 2 name", ["Field 1", "Field 2", "Field 3"], [data.Results[i].Properties[0].Value, data.Results[i].Properties[1].Value, elements[n]]);
            }
        }
    }
}
</script>