Simple Filter and Formula Not Identifying Null Values

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

Developer

Expression

Accepted answers

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]) )

All comments

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]) )

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

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

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

Quick Links

Tags

This months top contributors

atcodedog05 19458

Qiu 15866

MarqueeCrew 13708

apathetichell 13703