Havving issues updating fields in Data Extension with SSJS

Question

In Marketing Cloud -> Automation Studio -> Script Activity, I am trying to run a script that updates a column named "age" (dtype: integer) based on another column named "date of birth" (dtype: date). It is within a single data extension named "User".

The idea is to loop/iterate through all the subscribers/records, calculate the age of the given subscriber based on the column "date of birth" and then update the column "age" with the respective age. Thus, I want the update the column "age" based on a calculation on another column, "date of birth" (calculation is in the var "aged" within the code snippet). The primary key of the data extension is the SubscriberKey.

I have put together the following code, however it does not work and I feel I am missing something (I am totally new to SSJS).

  <script runat="server">
  Platform.Load('core', '1');

  var testDE = DataExtension.Init("User");
  var testDEData = testDE.Rows.Retrieve();

  for (var i = 0; i < testDEData.length; i++) {
      var subKey = testDEData[i].SubscriberKey;
      var dob = testDEData[i]."date of birth";
      var aged = testDE.FLOOR((TODAY() - "date of birth"__c)/365.2425);

      var processing = testDE.Rows.Update({age:aged}, ["SubscriberKey"], [subKey]);
  }
</script>

Do you have any suggestions? Highly appreciated! Please tell me if you need further information.

Answer 1

I honestly would instead recommend using a SQL query. You would run this in your automation instead of the Script Activity. This query would have the same source and target - your 'User' DE. The action for the query would be 'Update'.

Sample query:

SELECT SubscriberKey,
FLOOR(DATEDIFF(day,"date of birth", getdate()) / 365.2425) as age
FROM [User]

/* target: User */
/* action: update */

This is assuming that the field inside the DE containing the date of birth is literally named date of birth. If not, adjust this to the correct field name.

Now if you want to stick with SSJS, you should be able to correct your script like below:

  <script runat="server">
  Platform.Load('core', '1');

  var testDE = DataExtension.Init("User");
  var testDEData = testDE.Rows.Retrieve(); 
  //this will only pull 2500 rows max though

  for (var i = 0; i < testDEData.length; i++) {
      var subKey = testDEData[i].SubscriberKey;
      var dob = testDEData[i][date of birth];
      var today = new Date();
      var diffTime = today - dob; //returns difference in milliseconds
      var diffDays = diffTime / (1000 * 3600 * 24); //returns difference in days
      var age = Math.floor(diffDays / 365.2425);  //returns year difference, rounded down

      var processing = testDE.Rows.Update({"SubscriberKey":subkey}, ["Age"], [age]);
  }
</script>

The SQL will likely be more performant and does not have the max payload restriction that the script will. If you still want to stick with SSJS, I would look at using WSProxy to utilize the ContinueRequest feature, plus it will allow you to do bulk retrieve and uploads. As well, WSProxy is likely to be more performant than the Core functions.