#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!
Solved! Go to 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
@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!
@binuacs Thank you!
@joelmiller66 Much appreciated. This worked fast with my 3 years of data to transpose! Thank you!
@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.
@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.
@JPSeagull Here is a more dynamic solution based on the new YXMD you shared. My workflow is attached.