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

Return or tell if a column is all nulls?

brendafos
10 - Fireball

Is there a way Alteryx can return or tell you that a column is all nulls, that the column is empty?

 

 

14 REPLIES 14
JordanB
Alteryx
Alteryx

Hi @brendafos

 

I quickly made this workflow to remove null fields. Hopefully this should lead you down the right path.

 

Workflow build in 10.5

 

Best,

 

Jordan Barker

Solutions Consultant 

RodL
Alteryx Alumni (Retired)

And if you are just wanting the information instead of removal of the fields, you can get this info from the Field Summary tool.

brendafos
10 - Fireball

Thanks JordanB, I used it and it was grand - so grand!

I'm going to build this into many of my workflows!!!!

brendafos
10 - Fireball

JordanB -

I used your workflow to remove fields with no values.

It works well BUT it changes all data types to strings.  So, darn, that is not useful for me.

I looked at how it works and I think there is not a way to prevent this data type changes.

brendafos
10 - Fireball

RodL - Tell me more about the Field Summary tool, or is there a link that better explains how to use it?

 

I ran it and I see the Output but what shows which fields are empty?

Any suggestions on how to use the Output for formulas?

RodL
Alteryx Alumni (Retired)

Check out the Help for the tool. F1 is my favorite key! Smiley Wink

 

Out of the "O" anchor of the tool, you actually get data that you could use if desired that lists all the fields you selected in the configuration of the tool. Many statistics about the values in the fields...one is called "Percent Missing"...that should indicate which fields have all Null values.

JordanB
Alteryx
Alteryx

Hi @brendafos

 

I have attached an updated workflow which uses the ability to save the configuration of a select tool at the start of the workflow and upload after the crosstab so it makes sure the fields have the same data type at the start of the workflow and at the end. 

 

The field schema you save from the first select will then be loaded into the second select tool later in the workflow. 

 

Best,

 

Jordan Barker

Solutions Consultant 

 

brendafos
10 - Fireball

When I click on the last Select tool in your workflow it opens a Browse.  I guess it wants me to click on the origianl file?

But I am using a Directory tool and a Dynamic Input tool to open a bunch of files so there is no single file to click on.

 

 

JordanB
Alteryx
Alteryx

hi @brendafos

 

When you have read in all your files (Dynamic Input) you should now have a complete dataset with the data types you want at the end of your cleaned workflow.  

 

You now need to insert a select tool go to options within the configuration panel>>>Save/Load>>>Save field configuration. 

Pic 2.png

 

This should then launch your windows explorer. You need to save this field configuration file (.yxft)

 

Pic 3.png

 

After you have done the removing of the null fields (so after the last crosstab tool where the fields are all strings), you need to insert a new select tool. 

 

In here you need to go to options>>>Save/Load>>>Load Field Names & types

Pic 3.png

 

This will then prompt you saying 'This file has a different number of fields, Do you want to try loading anyway?'

 

Pic 4.png

 

You want to select yes and then this file will update the data types based on field name, giving you the same data type you had after the dynamic input (configuration you saved after the dynamic input_. 

 

You should see the changes reflected in Pink

pic 5.png

 

Best,

 

Jordan Barker

Solutions Consultant 

 

Labels