ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

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

finding nulls

becki
8 - Asteroid

i'm trying to identify, in hundreds of fields, which one have any nulls in them.  i tried this:

 

https://community.alteryx.com/t5/Data-Preparation-Blending/Only-show-me-rows-with-a-blank-field/m-p/...

 

but that gives me any record which has any null, in any field, in the entire record.  what i'm looking for is find all columns (fields) which have any nulls in them.  i tried a summarize -> selected all fields and did "count null" but even for fields which were completely empty, the result was 0.  this is on alteryx 11.5, if that matters.  thank  you in advance

 

 

becki kain

 

Dynamomo
11 - Bolide

Hi @becki,

Have you thought about using the Basic Data Profile tool in the Data Investigation tools?  I think it must have moved out of the Lab recently and into that category so you might not be aware of it.

You can run that on the file, then place a filter after where Name="Nulls" and Value !="0".  That will give you a list of the fields with nulls and the number of records with nulls in that field.

gnans19
11 - Bolide

@becki Try this one. You can control how many columns you want to consider.

 

Nulls.png

becki
8 - Asteroid

thanks!  I will and post back!

danrh
13 - Pulsar

If you strictly need a list of all fields with null values in them, this should get you pretty close:

 

image.png

 

The Transpose puts all the column names in one column with a second column with the values.  THEN I'd do a Summarize, grouping by Name and CountNull-ing the Values.

 

Hope it helps!

amavila
5 - Atom

This is awesome! It helped me a lot.

TGoncalves
6 - Meteoroid

This is a very simple and useful solution. 

 

Thank you very much @danrh

Labels