Remove NULL Columns - 2019.4

[suby]

Hi All,

I believe in the recent version of Alteryx you have the option to remove NULL columns on the Dat cleansing Tool.

We are in 2019.4 and i have some NULL values coming through and my requirement is to completely remove the NULL columns from the Data stream any thoughts.

[AngelosPachis · Accepted solution]

Hey @suby ,

You can transpose your data and then filter out the null values. For columns that are 100% null, that will remove all records, and hence when you cross tab back to the original table structure those columns will not be there

Input :

Output:

Hope that helps,

Angelos

[RishiK · Accepted solution]

@suby the attached workflow may help you.

[danilang]

Hi @suby 

Just a quick comparison of the two methods submitted above 

@AngelosPachis 

This method transposes all the data, removes the null records and then uses a cross tab to bring the data back to it's original shape without the null columns. 

Pros:

Easy to understand

Fairly quick

Cons :

Performs a cross tab operation on the complete data set

Renames the output columns replacing any characters that are not alpha or digits with underscores

Can reorder columns alphabetically

@RishiK 

This method transposes the data and then analyzes it by counting the number of null values in each column.  It then generates a new name for the null columns, renames the null columns in the original data using the Dynamic Rename and then removes the null columns from the dataset with the Dynamic Select.

Pros:

Faster since the the summarize operation is more efficient than the cross tab.

Doesn't rename or reorder the output columns

Cons:

More complex 

Summary

While both methods produce similar results, the dynamic rename and select is the preferred approach since it's faster and doesn't require field name cleanup and reordering

Dan

[suby]

Hi Dan,

Many thanks for the great explanation. 

Thank you will accept both as my solutions.

[suby]

Thank You.

[suby]

Thank you so much.

[MI1]

@danilang  or any one - I would like to order column based on the number of items per row, however when I used it is not working. Any solution can be done ?