Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Crosstab - Splitting row label to stay with corresponding value

john86901
5 - Atom

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    
InvoiceLine ItemTax TypeTJ NameTaxable AmountTax
00120State TaxArizona                                    500.00                       24.00
00120County TaxMaricopa                                    500.00                         3.00
00130State TaxArizona                                    125.00                         6.00
00130County TaxMaricopa                                    125.00                         0.75
00220State TaxWashington                                       50.00                         4.00

 

PREFERRED FORMAT      
InvoiceLine ItemTJ Name (County) County Taxable Amount  County Tax TJ Name (State) State Taxable Amount  State Tax 
00120 Maricopa                                       500.00                                         3.00Arizona                                  500.00                        24.00
00130 Maricopa                                       125.00                                         0.75Arizona                                  125.00                           6.00
00220   Washington                                     50.00                           4.00
2 REPLIES 2
JessieC
Alteryx
Alteryx

@john86901 

 

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

JessieC_0-1576112460969.png

 

john86901
5 - Atom

This works wonderfully.  Thank you!

Labels