Dynamically Select Fields based on how many null records they have

[aklassen3]

I am aggregating multiple datasets from different sources using the union tool. The number of extraneous fields in the datasets may change over time, so I want to dynamically select which fields to include when aggregating based on the percentage of records in that field are null. Any ideas on how to tackle this?

Field_Name	CountBlank_Value	CountNonBlank_Value	PCT_BLANK	KEEP_FIELD?
ACTUAL_SKU_PRICE	537	143188	0.373630197	Y
BUDGET_CENTER_CODE/DEPARTMENT_1	122722	21003	85.38667594	N
BUDGET_CENTER_CODE/DEPARTMENT_2	122722	21003	85.38667594	N
BUDGET_CENTER_CODE/_DEPARTMENT_1	124832	18893	86.85475735	N
BUDGET_CENTER_CODE/_DEPARTMENT_2	63406	80319	44.11619412	N
CHILD_ACCOUNT	0	143725	0	Y
COMPANY_ID	48737	94988	33.90989737	N
COMPANY_NAME	0	143725	0	Y
CONTRACT_INDICATOR	96109	47616	66.87006436	N
CORE_TAG	0	143725	0	Y
CURRENCY	139632	4093	97.15220038	N
DATE_ADDED	48963	94762	34.06714211	N
DELIVERY_ADDRESS	2	143723	0.001391546	Y
DELIVERY_CITY	88796	54929	61.78187511	N
DELIVERY_STATE	3220	140505	2.240389633	Y
DELIVERY_ZIP	88796	54929	61.78187511	N
GRANDPARENT	0	143725	0	Y

[BrandonB · Accepted solution]

Another user in the community built a macro that performs this exact function! Here is the link to their post and their macro is attached:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Remove-Null-Valued-Columns/td-p/424710

[grossal · Accepted solution]

Hi @aklassen3,

you are on a good road, you just need a Dynamic Rename and Dynamic Select tool to finish the job. Here is how it would look:

What happens:

- We use the Dynamic Rename to update the Meta Data Description of each column with the 'Keep' field from your table

- We can than use the Dynamic Select to filter out all Values with 'N'

Settings:

I'll attach the workflow for reference. Let me know if it worked.

Best

Alex

[aklassen3]

Thanks so much, @BrandonB . This definitely solves the roadblock I was having.

[aklassen3]

Thanks @grossal ! This is very helpful.