Excel to Alteryx with Transposing Columns to Rows
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
#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.
- Labels:
- Output
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@binuacs Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@joelmiller66 Much appreciated. This worked fast with my 3 years of data to transpose! Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@JPSeagull Here is a more dynamic solution based on the new YXMD you shared. My workflow is attached.
