0

The business needs to create multiple single DEs and then merge them using automation into a single master DE. This happens on a weekly basis This master DE will be then used in Journey Builder.

The issue we are facing is that the number of DEs to be created each week can vary from week to week. I there a way to detect the number of single DEs created for a week automatically and then run an SQL automation query in automation studio to merge those DEs into the master DE?

Thanks, NN

1 Answer 1

0

Assuming that your DEs are created in one folder, you could write the below code to fetch all DEs using 'CreatedDate' attribute on the DataExtension Object.

The second part is to use a ssjs script to create/update the query. Your overall code should look something like this below: Debug and modify the code as per your need

   <script runat="server">

Platform.Load("core","1.1.1");
try
{
  //If the Data extensions are not saved in one folder
   var lastWeek = new Date((new Date()).valueOf() - 1000*60*60*168);
  var results = DataExtension.Retrieve({Property:"CreatedDate",SimpleOperator:"greaterThan",Value:lastWeek});
    var querybase = "select * from ";
  var query = "";
  for (var i = 0; i < results.length; i++)
  {
    if(results[i].CategoryID == "yourfolderid") //Only if the Data Extension is in your folder
    {
    var Name = results[i].Name;     
     queryText = querybase + Name + " UNION " + query + "\n";
    }
  } 
  
  //Removing the last union
  var lastIndex = queryText.lastIndexOf("UNION");
    queryText = queryText.substring(0, lastIndex);
  Write(queryText);
}
createOrUpdateQuery("CreateTargetDE","queryText",folder_idof_SQLFolder)
  
  function createQuery(queryName, queryText, categoryID){

    var guid = Platform.Function.GUID();
    var name = getQueryName(queryName);

    // var options = {SaveOptions: [{'PropertyName': '*', SaveAction: 'UpdateAdd'}]};

    var query = {
        Name: name
        , CustomerKey: guid
        , Description: "Generated queries for analysis"
        , QueryText: queryText
        , TargetType: "DE"
        , DataExtensionTarget: {
            Name: TARGET_DE_NAME
            , CustomerKey: TARGET_DE_CUSTOMER_KEY
        }
        , CategoryID: categoryID
        , TargetUpdateType: "Update"
    }

    if(debug){
        Write("<hr></hr>Query ");
        Write(Stringify(query));
    }

    var res = prox.createItem("QueryDefinition", query);
    return res;
}

function updateQuery(queryName, queryText, categoryID){
    var cols = ["Name","Description", "CategoryID", "queryText", "CreatedDate", "ObjectID", "Status", "CustomerKey"];
    var filter = {
        Property: "Name"
        , SimpleOperator: "equals"
        , Value: getQueryName(queryName)
    };
    var opts = null;
    var props = null;
    if(categoryID){
        filter = {
            LeftOperand: {
                Property: "CategoryID"
                , SimpleOperator: "equals"
                , Value: categoryID
            }
            , LogicalOperator:"AND"
            , RightOperand: filter
        };

    }
    filter = {
            LeftOperand: {
                Property: "Status"
                , SimpleOperator: "equals"
                , Value: "Active"
            }
            , LogicalOperator:"AND"
            , RightOperand: filter
        };

    return prox.retrieve("QueryDefinition", cols, filter, opts, props) ;
}

function createOrUpdateQuery(queryName, queryText, categoryID){
    var res = updateQuery(queryName, queryText, categoryID);
    if(res.Status == "OK" && res.Results.length > 0){
        return res.Results[0].ObjectID;
    } else {
        res = createQuery(queryName, queryText, categoryID);
        if(res.Results && res.Results[0]){
            var objectID = res.Results[0].Object.ObjectID;
            var statusMessage = res.Results[0].StatusMessage;
            var statusCode = res.Results[0].StatusCode;
            if(statusCode == "OK" && objectID){
                return objectID;
            }
            if(debug){
                Write("<hr></hr>");
                Write(Stringify(statusMessage));
                Write(Stringify(result));
            }
        };
    }
}

 catch(e){
Write(Stringify(e));
}

</script>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.