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 |
Solved! Go to 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
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
Thanks so much, @BrandonB . This definitely solves the roadblock I was having.
Thanks @grossal ! This is very helpful.