ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a time due to scheduled maintenance on Thursday, April 22nd. Please plan accordingly.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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

 

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
17 - Castor
17 - Castor
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
16 - Nebula
16 - Nebula

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
17 - Castor
17 - Castor

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