1

I am trying to filter a data extension to only return certain rows via an SSJS automation.

It works fine if I set the SimpleOperator to "equals" and the Value to a static value.

What I really want to be able to do is filter rows with NULL in a particular field. I tried "notEquals" but it doesn't seem to work. e.g.

// FILTER ROWS THAT DON'T HAVE A STATUS OF PROCESSED
var TARGET_DE = DataExtension.Init("7AAB4709-88A6-4C4E-B153-901351D820A1");
var Filter = {Property:"Status",SimpleOperator:"notEquals",Value:"processed"};
var Data = TARGET_DE.Rows.Retrieve(Filter);

(Note: If the value isn't processed it is NULL)

I suspect I'm not able to use the SimpleOperator filter for NULL values.

2

I believe you can use the simple operator of isNull to achieve this.

You can use operators when building filters. The operators below work for all objects:

  • equals
  • notEquals
  • greaterThan
  • lessThan
  • isNull
  • isNotNull
  • greaterThanOrEqual
  • lessThanOrEqual
  • between
  • IN
  • like

If not, then you could try:

var Filter = {Property:"Status",SimpleOperator:"equals",Value:""};

This should make the value default to NULL and SHOULD only pull records accordingly. (Have not tested)

Your notEquals statement is requiring a value (which does not include NULL or EMPTY) that is not the declared value.

There are also a couple other not as elegant solutions you could look for. See this answer for some examples: How do I pull rows with non-null values in a field in FuelSDK?

| improve this answer | |
  • I managed to get this working. Based on your suggestion I used: var Filter = {Property:"Status",SimpleOperator:"equals",Value:" "}; However to get it to work I had to use a space in the value like " " Also in this documentation based on the SDK null filters aren't an option but I'm not sure if it applies. developer.salesforce.com/docs/atlas.en-us.mc-sdks.meta/mc-sdks/… – Travis Naughton Oct 18 '17 at 23:43
  • 1
    You can use var filter = {Property:"Field name",SimpleOperator:"isNull"}; – Josh de Blank Sep 3 '19 at 5:16

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.