0

Using Marketing Cloud:

I have a data extension with a date field. On a cloudpage, using ampscript, i need to get all the records from that data extension where the date field is between/including 2 dates.

The LookupRows function seems to be the best available but i can only directly compare values to each other, it can't use a "between" statement.

What would be the best way to achieve this ? ( Other than executing a LookupRows for each date separately )

4
  • You want to use it in messages or in CloudPages/Microsites? Dec 12, 2017 at 15:42
  • CloudPage/Microsite
    – Timon
    Dec 12, 2017 at 16:18
  • How many rows are we talking about? Dec 12, 2017 at 16:29
  • Should be between 0 and 100 or so
    – Timon
    Dec 12, 2017 at 17:11

1 Answer 1

2

You will likely need to utilize SSJS via the Rows.Retrieve function.

Sample script:

var yourDE = DataExtension.Init("yourDE");
var complexfilter = {
                LeftOperand:{
                    Property:"Date",
                    SimpleOperator:"greaterThan",
                    Value: {{Start Date}}
                },
                LogicalOperator:"AND",
                RightOperand:{
                    Property:"Date",
                    SimpleOperator:"lessThan",
                    Value: {{End Date}}
        }};
var filterdata = yourDE.Rows.Retrieve(complexfilter);

Be aware though, this will only allow a maximum return of 2500 rows for each retrieve.

To get around this, you will need to do 1 of two things.

  1. Utilize SOAP API to do this Retrieve Request and then use ContinueRequest property.

Sample Continue Request in SSJS:

do { 

    //your Retrieve Request API here

    rr.ContinueRequest = requestId;

} while (runstatus == "MoreDataAvailable")

  1. Create a Query in SFMC and run it prior to each pull and then utilize the resulting DE as a reference for your AMPscript - I would include an attribute named 'Secret' with a value of 1 - for easy reference in your lookup.

Sample SQL:

SELECT Column1, Column2, 1 as Secret
FROM yourDE
WHERE Date >= '2017/02/25' and Date <= '2017/12/07'

You must log in to answer this question.

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