Alteryx newbie here. I am wondering how complicated the data reformatting below would be. The new data set will have 1 row for the Invoice/Line Item combo.
Specifically, how do I split the TJ Name column to align with the appropriate tax type?
Attached is sample date in the current format. Please let me know if my question is not clear or I can provide any additional info.
Thank you!
John
CURRENT FORMAT | |||||
Invoice | Line Item | Tax Type | TJ Name | Taxable Amount | Tax |
001 | 20 | State Tax | Arizona | 500.00 | 24.00 |
001 | 20 | County Tax | Maricopa | 500.00 | 3.00 |
001 | 30 | State Tax | Arizona | 125.00 | 6.00 |
001 | 30 | County Tax | Maricopa | 125.00 | 0.75 |
002 | 20 | State Tax | Washington | 50.00 | 4.00 |
PREFERRED FORMAT | |||||||
Invoice | Line Item | TJ Name (County) | County Taxable Amount | County Tax | TJ Name (State) | State Taxable Amount | State Tax |
001 | 20 | Maricopa | 500.00 | 3.00 | Arizona | 500.00 | 24.00 |
001 | 30 | Maricopa | 125.00 | 0.75 | Arizona | 125.00 | 6.00 |
002 | 20 | Washington | 50.00 | 4.00 |
Solved! Go to Solution.
Here is what I came up with -
1. Filter null rows
2. Remove null columns
3. Filter on Tax Type to split the data into County vs. State
4. Join on Invoice and Line Item
5. Select to rename fields to align
6. Union Washington state record back with other records
This works wonderfully. Thank you!