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>