0

I want to use LookupOrderedRows and return rows between a start and end date (last month range). What is the best way to handle using Ampscript?

I can add one date to the where using

"DateField", @varStartDate,

But if I add the below to add additional where filters

"DateField", @varStartDate,"DateField", @varEndDate,

I get this error:

"Duplicate Key Field Name: DateField"

EDIT: Also I tried the range as the var for the LookupOrderedRows using

set @strRange = StringToDate(Format(DateDiff(@strStartDate, @strEndDate, "M"),"MM/dd/yyyy"))

and then in the LookupOrderedRows just using

"DateField", @strRange

But that got an error as well.

0

You're looking for a "between" type lookup which is a limitation of the AMPscript. You just cannot do it directly. A couple of workarounds:

  1. Create a DataFilter (where you can use between functionality) within the application, then call AMPscript function ExecuteFilter to get your rowset. OR

  2. Write a query activity, putting the results in a new Data Extension, and do your lookup there.

Hope this helps!

  • I have all of my processing logic within one FOR loop and do not really want to create a separate data set for this small addition. I have the lookup working so I can pull two months back. Since I can't add the between to the lookup can't I add something like this: IF Field(@currentRow, "DateField") < StringToDate(Format(DateAdd(Now(), -1, "M"),"MM/dd/yyyy")) and then within that if add my logic just for the range that matches older than one month? Thanks – james May 14 '14 at 15:06
  • IF you are working with small amounts of data, this would work fine. If however, we're talking about looping through millions of records to get a data set of 500, it's less than optimal. In that case, I would choose the ExecuteFilter option. DataFilters allow some dynamic options. So, you can do a filter on "dateColumn before today" AND "dateColumn after today - 60" within the same datafilter, so you can reuse it easily. All you would need to do in your code is use ExecuteFilter instead of the FOR loop – Timothy May 14 '14 at 15:22
  • Ok, thanks. It is a small data set but I think the Data Filter / ExecuteFilter may be a cleaner solution. So I think I will give that a try. I have never had a need to use the data filters before. They do not alter any of the existing data extensions correct. They only return a filtered list of the existing DE when the filter is called and never actually touches the DE data correct? Thanks – james May 14 '14 at 16:18
  • That is correct, no altering of the DE data. – Timothy May 14 '14 at 16:22
  • One additional question just to clarify. The data extensions it is important that they are run once a day at pre-specified times through the programs setup (or else the data will be off). When a filter is called those DE's are not refilled correct but just filtering current data at that time in the designated DE? Thanks – james May 14 '14 at 16:34
0

Without yet resorting to psuedo coding this (sorry, so busy it's NUTS). I'm wondering if the overall script you author can run the following steps. Considering that what we need is to limit the records where the date is (@M = "thismonth") I'll use today as an example and this month is 05/14. So you want all records where date is => 4/1/14 and <5/1/14. (@beginDate and @endDate)

First, determine the date of now() for a basis of determining last month vs. this month.

Next, using dateadd()and datepart() you may be able to create parameters such as Now "@M" = "05" use an adddate - 1 M is last month. (call that "@A") Now the records you're seeking are either equal to or greater than 4/1 ~ x. (but not YET limited to prior to 5/1/##)

To determine @M = of course it is the month part of "now()" so the end limit date is "< @M" (to eliminate variables where last day of last month could be "28 ~ 31 just make sure it's less than the start date of the following month".

Considering that you can use multiple parameters in a LOOKUPROWS() function you can lookup the rows where date is >= 4/1/## (@beginDate) AND < 5/1/## (@endDate). I think that summarizes the logic and functions you will need.

Functions: Now(), DateAdd(), DatePart(), LookupOrderedRows() and Conditional Syntax.

I feel like this can work in AMPscript, be interested if someone more experienced knows why it wouldn't. I agree where to me creating an intermediate Data extension would be a large extra step (but probably simple when we get fluent). Filters are also easy to use and may be an easy method to summarize the date logic. Good luck and I hope it helps.

  • That is originally what I wanted to do. Originally I wanted to reference DateField twice with the multiple conditions to check against but it gave an error because I used the key more than once (unless the syntax is different for multiple parameters against one key) but it doesn't sound like that was possible directly through the lookuporderrows call. That would have been the optimal way to handle. – james May 14 '14 at 18:56

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.