Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Simple Filter and Formula Not Identifying Null Values

Empower49
8 - Asteroid

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

 

8 REPLIES 8
CharlieS
17 - Castor
17 - Castor

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
ACE Emeritus
ACE Emeritus
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
ACE Emeritus
ACE Emeritus

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
8 - Asteroid

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

Empower49
8 - Asteroid

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

Empower49
8 - Asteroid

Thank you! I appreciate the advice.

CharlieS
17 - Castor
17 - Castor

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
ACE Emeritus
ACE Emeritus

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

Labels