Alteryx Designer Discussions

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

Simple Filter and Formula Not Identifying Null Values

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

 

Alteryx Certified Partner
Alteryx Certified Partner

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

Alteryx Certified Partner
Alteryx Certified Partner
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
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

8 - Asteroid

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

8 - Asteroid

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

8 - Asteroid

Thank you! I appreciate the advice.

Alteryx Certified Partner
Alteryx Certified Partner

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.

Alteryx Certified Partner
Alteryx Certified Partner

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