Excel to Alteryx with Transposing Columns to Rows

[JPSeagull]

#Hi. I have been testing all kinds ways of using transpose and cross tabs and I am not able to get exactly the view I need to write data to xydb file.

I have three years of data in an Excel file that summarizes two values each date and it appends to the right of 11 categories. This is how sample of the data looks in Excel:

Date >	8/8/2022	8/8/2022	8/7/2022	8/7/2022	8/4/2022	8/4/2022
Category	Site - Total	Site - Sales	Site - Total	Site - Sales	Site - Total	Site - Sales
Within 7 Days of Due	54	$2,104.15	55	$3,892.93	57	$3,292.99
8 to 15 Days to Due	55	$1,933.28	54	$1,786.07	54	$2,104.15
Up to 7 Days Past Due	58	$5,023.44	57	$3,234.66	54	$1,783.45
8 to 15 Days Past Due	54	$2,149.30	56	$2,257.83	58	$6,926.74
16 to 30 Days Past Due	59	$6,512.28	57	$4,542.72	58	$5,593.95
31 to 60 Days Past Due	56	$1,383.16	56	$1,383.16	57	$1,436.25
More Than 60 Days Past Due	58	$7,311.92	58	$7,311.92	58	$7,072.57
16 to 30 Days to Due	78	$25,223.85	67	$27,102.52	73	$34,765.12
31 to 60 Days to Due	161	$178,370.49	127	$93,458.73	132	$185,245.45
> 60 Days to Due	568	$1,189,098.92	387	$872,103.19	557	$1,435,681.07
No Category Data	176	$53.00	190	$53.00	119	$53.00

This is how I need it to look so I can pivot it in Tableau for visualizations:

Date	Category	Site - Total	Site - Sales
8/8/2022	Within 7 Days of Due	54	$2,104.15
8/8/2022	8 to 15 Days to Due	55	$1,933.28
8/8/2022	Up to 7 Days Past Due	58	$5,023.44
8/8/2022	8 to 15 Days Past Due	54	$2,149.30
8/8/2022	16 to 30 Days Past Due	59	$6,512.28
8/8/2022	31 to 60 Days Past Due	56	$1,383.16
8/8/2022	More Than 60 Days Past Due	58	$7,311.92
8/8/2022	16 to 30 Days to Due	78	$25,223.85
8/8/2022	31 to 60 Days to Due	161	$178,370.49
8/8/2022	> 60 Days to Due	568	$1,189,098.92
8/8/2022	No Category Data	176	$53.00
8/7/2022	Within 7 Days of Due	55	$3,892.93
8/7/2022	8 to 15 Days to Due	54	$1,786.07
8/7/2022	Up to 7 Days Past Due	57	$3,234.66
8/7/2022	8 to 15 Days Past Due	56	$2,257.83
8/7/2022	16 to 30 Days Past Due	57	$4,542.72
8/7/2022	31 to 60 Days Past Due	56	$1,383.16
8/7/2022	More Than 60 Days Past Due	58	$7,311.92
8/7/2022	16 to 30 Days to Due	67	$27,102.52
8/7/2022	31 to 60 Days to Due	127	$93,458.73
8/7/2022	> 60 Days to Due	387	$872,103.19
8/7/2022	No Category Data	190	$53.00
8/4/2022	Within 7 Days of Due	57	$3,292.99
8/4/2022	8 to 15 Days to Due	54	$2,104.15
8/4/2022	Up to 7 Days Past Due	54	$1,783.45
8/4/2022	8 to 15 Days Past Due	58	$6,926.74
8/4/2022	16 to 30 Days Past Due	58	$5,593.95
8/4/2022	31 to 60 Days Past Due	57	$1,436.25
8/4/2022	More Than 60 Days Past Due	58	$7,072.57
8/4/2022	16 to 30 Days to Due	73	$34,765.12
8/4/2022	31 to 60 Days to Due	132	$185,245.45
8/4/2022	> 60 Days to Due	557	$1,435,681.07
8/4/2022	No Category Data	119	$53.00

but the closest I can get is this: 

I have to figure out how to get the columns correct as well as resolve the dates adding the "_2" and then moved over to first column. 

Any advice will be greatly appreciated. Thanks!

[AngelosPachis · Accepted solution]

Hi @JPSeagull ,

When reading data from your excel input, one thing that will help is if you select option no6 so Alteryx reads the first row as data and not actual headers

Then you can split into two streams, one to process the first two rows of data and another one to process the main table

Hope that helps,

Angelos

[binuacs]

@JPSeagull One way of doing this

[joelmiller66]

Hi @JPSeagull  Here is another way to solve it.

[JPSeagull]

@AngelosPachis  - Absolutely Perfect! Thank you. I tweaked it by adding a Unique tool as there were duplicates and triplicates of the same data and dates and placed it right after the Join Tool. Also added a Sort Tool.  Much appreciated!

[JPSeagull]

@binuacs Thank you! 

[JPSeagull]

@joelmiller66 Much appreciated. This worked fast with my 3 years of data to transpose! Thank you!

[JPSeagull]

@AngelosPachis How would I resolve the workflow if the next Excel file has more than 1 Total and 1 Sales column for the same Date with different Categories? I tested different ways, but I cannot seem to figure out how to group the data.

[JPSeagull]

@joelmiller66 I have another Excel file, but this data has three ownership sites with each having a totals and dollars amount for each day. How would that work? I tried to use your version which worked perfect for the two columns, but it doesn't see to find Category as a field.

[joelmiller66]

@JPSeagull  Here is a more dynamic solution based on the new YXMD you shared. My workflow is attached.