Filter a data extension in SSJS

Question

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.

Answer 1

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?