0

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

http://help.exacttarget.com/en/documentation/exacttarget/content/ampscript/ampscript_syntax_guide/data_extension_ampscript_functions/#LookupRows

Example:

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
  • 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
    Commented Sep 23, 2015 at 17:43

2 Answers 2

3

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

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

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.

e.g.

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

               Platform.Variable.SetValue("@Status",status);
</script>
<div>%%=OUTPUT(@Status)=%%</div>
<script runat=server>
      }
 }
</script>

You must log in to answer this question.

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