Dynamically Select Fields based on how many null records they have

Question

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_NameCountBlank_ValueCountNonBlank_ValuePCT_BLANKKEEP_FIELD?ACTUAL_SKU_PRICE5371431880.373630197YBUDGET_CENTER_CODE/DEPARTMENT_11227222100385.38667594NBUDGET_CENTER_CODE/DEPARTMENT_21227222100385.38667594NBUDGET_CENTER_CODE/_DEPARTMENT_11248321889386.85475735NBUDGET_CENTER_CODE/_DEPARTMENT_2634068031944.11619412NCHILD_ACCOUNT01437250YCOMPANY_ID487379498833.90989737NCOMPANY_NAME01437250YCONTRACT_INDICATOR961094761666.87006436NCORE_TAG01437250YCURRENCY139632409397.15220038NDATE_ADDED489639476234.06714211NDELIVERY_ADDRESS21437230.001391546YDELIVERY_CITY887965492961.78187511NDELIVERY_STATE32201405052.240389633YDELIVERY_ZIP887965492961.78187511NGRANDPARENT01437250Y

dynamic select.xlsx

BrandonB · Accepted Answer

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 Answer

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