Simple Filter and Formula Not Identifying Null Values

[Empower49]

Hello. I have a filter and formula (below) and Alteryx is not identifying the null values, it returns data as if every record has a value in the EXT_TRADE_ACCT_NBR field, which is not the case. This field is formatted as V_string. Do you have advice? Thanks.

Simple filter:

IsNull([EXT_TRADE_ACCT_NBR         ])

Simple formula:

IF IsNull([EXT_TRADE_ACCT_NBR         ])

THEN [ID1        ]+ [ID2]

ELSE "ok"

ENDIF

[CharlieS · Accepted solution]

If a field is null, it will display a grey "[Null]" in the results window. Another possibility is that the field is empty and that can be tested for using the isempty() function. Try adding that to your condition and see if that works. 

( IsNull([EXT_TRADE_ACCT_NBR]) OR IsEmpty([EXT_TRADE_ACCT_NBR]) )

[BenMoss]

As an FYI isempty() tests for both NULL and blank fields so you don't have to do ISNULL() OR ISEMPTY(), ISEMPTY() alone would suffice.

Ben

[jdunkerley79]

IsEmpty includes checking for null.

One addition that can cause false positives is white space so I suggest:

IsEmpty(TRIM([EXT_TRADE_ACCT_NBR]))

This will match Nulls, empty strings and white space

[Empower49]

Genius! I knew it was something simple. The "Is empty" solved the problem. Thanks VERY much!

[Empower49]

Thanks Ben! I didn't know that, so appreciate you sharing your expertise.

[Empower49]

Thank you! I appreciate the advice.

[CharlieS]

Thanks for teaching me something @BenMoss and @jdunkerley79. My order of testing had always been null then empty. Never though to reverse it and discover that for myself.

[BenMoss]

We can thank @TuvyL for this one! It was one of her questions for the ace race session at Inspire Europe, I had no idea either and approached it the same way as you @CharlieS!

Ben