0

I am using a cloudpage to display some general metrics on a data extension.

Having trouble displaying rowCount based on a field called entry_date

%%[ var @count, @today
SET @today = Format(Now(), "MM d, yyyy")
SET @count = RowCount(LookupRows('PlayTogetherWinTogether_Entries_Main','entry_date', @today))


]%%

%%=v(@count)=%%

A sample of the date of entry displays in the data extension as Monday, December 11, 2017 11:32 PM.

Basically just want to grab new records from today.

1

The simplest way (by sticking with AMPScript) is to use a lookuprows to get all your entries, and then use a FOR with IF ELSE statement. This will only work though if you have a universal identifier in the data. E.g. I usually use a column named 'secret' that defaults to a value of 1.

If you do not already have a field like this, what I would recommend is first to add a nullable field named 'secret' with a default value of 1 to 'PlayTogetherWinTogether_Entries_Main'. Then you can either create a SQL Query to update the value or export and import with the update made in excel or similar editor.

Example SQL:

SELECT p.*, '1' as secret
FROM PlayTogetherWinTogether_Entries_Main as p

Then select the target DE as 'PlayTogetherWinTogether_Entries_Main' and update type as 'Update'.

Below would be sample AMPScript:

%%[ VAR @count, @today, @rows

SET @count = 0
SET @today = Format(Now(), "MM d, yyyy")
SET @rows = LookupOrderedRows('PlayTogetherWinTogether_Entries_Main', DataExtensionRowCount('PlayTogetherWinTogether_Entries_Main'), 'secret', 1))

FOR @i = 1 to Rowcount(@rows) DO

  VAR @entry_date
  SET @entry_date = Field(Row(@rows, @i), 'entry_date')

  IF FORMAT(@entry_date, "MM d, yyyy") == @today THEN

   SET @count = ADD(@count, 1)

  ENDIF

NEXT @i

]%%

%%=v(@count)=%%

It is not as efficient as ROWCOUNT and LookupRows, but it should do the trick.

I have not tested the sample script, so if it throws an error, let me know and I will adjust accordingly.

0

Unfortunately, since SFMC stores the dates in datetime format, you will need to know the exact time of the row you want to retrieve. You can use API filters, however it will require more code, and the use of InvokeRetrieve function.

  • Could I just do a range and grab all the records that fall within that range regardless of the time associated? – Tom Callahan Feb 7 '18 at 18:15
  • Or utilize the CAST function? – Tom Callahan Feb 13 '18 at 19:43

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.