0

I have a Data Extension, All_campaigns, that contais:

cod_id name status city campaign
1472 Anna Ok Paris Perfum_01
1472 Anna Ok Paris Perfum_02
1472 Anna Ok Paris Perfum_03
1473 Rob Ok New York Perfum_03
1474 Sam Ok Paris Perfum_01
1475 Luke Ok Sao Paulo Perfum_01

And i'm trying to split that Data Extension by Campaign name column, with the name of the campaign like that:

New Data Extensions Generated by SSJS:

Perfum_01:

cod_id name status city campaign
1472 Anna Ok Paris Perfum_01
1474 Sam Ok Paris Perfum_01
1475 Luke Ok Sao Paulo Perfum_01

Perfum_02:

cod_id name status city campaign
1472 Anna Ok Paris Perfum_02

Perfum_03:

cod_id name status city campaign
1472 Anna Ok Paris Perfum_03
1473 Rob Ok New York Perfum_03

I tried something like that, but didn't worked. i'd be very thankful with help.

<script runat="server">

    Platform.Load("core","1.1.1");
    // Data Extension to split
    var dataExtensionName = "all_campaigns";
    
    // Column name to split by
    var splitByColumn = "campaign";
    
    // Retrieve the Data Extension you want to use as a base
    var deName = DataExtension.Init(dataExtensionName);
    
    // Create a new Data Extension based on the existing one
    var newDe = deName.Add({
      Name: 'MyNewDataExtension',
      Description: 'Data Extension created from MyDataExtension'
    });
    
    // Get the distinct values from the column
    var distinctQuery = 'SELECT DISTINCT ' + splitByColumn + ' FROM ' + dataExtensionName;
    var distinctRows = Platform.Function.Query(distinctQuery);
    
    // Loops through each distinct value and create a new data extension
    while (distinctRows.next()) {
      var distinctValue = distinctRows.get(splitByColumn);
      var newDataExtensionName = dataExtensionName + ' ' + distinctValue;
    
      // Check if the data extension exists
      if (DataExtension.Init(newDataExtensionName).Status == 'Not Found') {
        var createRequest = {
          "Name": newDataExtensionName,
          "CustomerKey": newDataExtensionName,
          "Fields": [var cols = deName.Columns.Retrieve();
    
    for (var i = 0; i < cols.length; i++) {
      newDe.Columns.Add(cols[i].Name, cols[i].DataType);
    }]
        };
        var createResult = Platform.Function.CreateDataExtension(createRequest);
      }
    
      // Populate the new data extension
      var queryString = 'SELECT * FROM ' + dataExtensionName + ' WHERE ' + splitByColumn + ' = \'' + distinctValue + '\'';
      var queryRows = Platform.Function.Query(queryString);
      var rowsToInsert = [];
    
      while (queryRows.next()) {
        var row = {};
        row[splitByColumn] = queryRows.get(splitByColumn);
        rowsToInsert.push(row);
      }
    
      var insertResult = Platform.Function.InsertData(newDataExtensionName, rowsToInsert);
    }
    </script>
3
  • 1
    When you write: it didn’t work, can you please be more specific in what went wrong? Jan 30 at 16:54
  • @LukasLunow the automation returned: "ERROR: A runtime error occurred while the SSJS Activity executed the SSJS script. Please review the SSJS activity to ensure that errors are handled properly."
    – lucesv
    Jan 30 at 17:04
  • Please read this great guide about debugging SSJS: mateuszdabrowski.pl/docs/ssjs/debugging-ssjs Jan 30 at 20:04

0

You must log in to answer this question.

Browse other questions tagged .