0

I am trying to keep a weekly count of the number of records in a number of data extensions, in another data extension. But the limitations of SFMC's SQL are too much for my limited knowledge of SQL, so currently I ended up creating a series of SQL queries:

  1. SELECT CAST(GETDATE() AS DATE) AS Date as an Update action on my target DE, so it will create a new record if the query is run on a 'new' date. (come to think of it, this one isn't needed, I think?)

  2. SELECT CAST(GETDATE() AS DATE) AS Date, COUNT(*) FROM Source_DE as an Update action on my target DE, to update the record of 'today' with a count.

And I repeat this for a few more DE's.

I've searched for better ways to do it, but every other solution I've found is simply not possible in SFMC. While this works, is there a more elegant way to do the same?

1 Answer 1

2

You could use the DataExtensionRowCount() AMPscript function. In order to make it more useful, you can wrap it in a SSJS function like this:

function getDERowCount(DEName) {
  
    var rowCount = 0;

    try {
      
      rowCount = Platform.Function.TreatAsContent("%"+"%"+"=DataExtensionRowCount"+"(\""+DEName+"\")="+"%"+"%");
      
    } catch(e3) {
      
      if (debug) {Write("<br>e3: " + Stringify(e3))}

      return 0;
      
    }
    
    return rowCount;
  
}

If you include this in a Script Activity on a CloudPage you could iterate through a list of DEs and output their size.

I use this function (along with a folder path one) to maintain a Data Extension inventory DE. It's a helpful data-set for reporting and inventory.

2
  • I am a complete novice to SSJS, or to JS in general, it somehow doesn't compute in my brain :D But perhaps I could try it! This does require you to visit the CloudPage, correct? Because I want to execute the counts at a regular interval (I've now set up an automation with that series of SQL queries which executes every Monday 6am to keep a weekly count). Commented Feb 23 at 13:54
  • You can develop it in a CloudPage and implement it in a Script Activity in your Automation. Both the CloudPage and Script Activity will let you run the SSJS code. Commented Feb 23 at 16:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .