Is it possible to use negative match criteria with LookupRows()?



A DE has a status column with 3 possible values:

  • Active
  • Closed
  • Pending

Would like to use LookupRows() to return all rows where status is NOT == "closed".

  • 1
    I'm not sure how you are intending to use the retrieved rows, but because its not possible in AMPscript you might consider using a Query Activity to filter the DE and then use that for what you need. – Jon Sakas Sep 23 '15 at 17:43

The lookup function only matches on single values. To lookup all Active and Pending records (aka not Closed), two separate lookup calls would be needed.

I've ran into similar situation on past projects. A couple different approaches I took are as follows.

Approach #1

Run a query on the data extension and set a flag in a new field for the rows you want to lookup.

select Status, 1 as [NewField]
from [DataExtensionName]
where status not ('Closed')

The above query assumes a numeric field named NewField has already been added to the data extension. Once this query runs, you should be able to use the Lookup() AMPScript function to retrieve all rows with a NewField value of 1.

Approach #2

The other approach would be to perform a filter on the data extension. You can do this using SSJS as Gortonington suggests. You can also do this via AMPScript by making an API call with the InvokeRetrieve() function.

Reference: Accessing API Functions Using the Function Object

| improve this answer | |
  • Is approach #2 something that can be used for emails at time of send? – Jerry Sep 23 '15 at 21:57
  • Yes, you can use the AMPScript API Functions in an email. – Adam Spriggs Sep 24 '15 at 13:36
  • I was not aware of the InvokeRetrieve() function. Thanks David for the info! – Gortonington Sep 25 '15 at 17:51

To my knowledge this is not possible in AMPscript. I believe using SSJS you should be able to though. Using DataExtension > Rows > Retrieve([filter]).

var statusDE = DataExtension.Init("statusDE");
var filter = { Property:"Status", SimpleOperator:"notEquals", Value:"Closed" }
var filterdata = statusDE.Rows.Retrieve(filter);

From there if you need to transfer the variables into AMPscript, you should use the "Platform.Variable.SetValue" function.


<script runat=server>
  if(filterdata && filterdata.length > 0) {
          for(var i=0; i<filterdata.length; i++) {
               var status = filterdata[i]["Status"];

<script runat=server>
| improve this answer | |

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.