We are celebrating the 10-year anniversary of the Alteryx Community! Learn more and join in on the fun here.
Start Free Trial

Alteryx Designer Desktop Discussions

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

Expanded Help on Previous Topic - Excel Input File Transposing multiple columns to rows

JPSeagull
8 - Asteroid

I was given an old Excel file where the user added 6 columns each day over last 3 years. So it goes way to the right. I have this data now below as you can see there are 11 categories and there are 3 locations (Site, Region and National) with each having a Total and Sales column added each day. I need to stack the data by rows so I can pivot it in Tableau. I am only sharing 3 days of reporting so you can imagine what 3 years looks like. And this is fake data....it's all fabricated, but it resembles what I have.

 

 

Date >8/8/20228/8/20228/8/20228/8/20228/8/20228/8/20228/7/20228/7/20228/7/20228/7/20228/7/20228/7/2022
CategorySite - TotalSite - SalesRegion - TotalRegion - SalesNational - TotalNational - SalesSite - TotalSite - SalesRegion - TotalRegion - SalesNational - TotalNational - Sales
Within 7 Days of Due54$2,104.15187$5,557.15518$6,844.1555$3,892.9389$6,025.93137$6,615.93
8 to 15 Days to Due55$1,933.28188$5,386.28519$6,673.2854$1,786.0788$3,919.07136$4,509.07
Up to 7 Days Past Due58$5,023.44191$8,476.44522$9,763.4457$3,234.6691$5,367.66139$5,957.66
8 to 15 Days Past Due54$2,149.30187$5,602.30518$6,889.3056$2,257.8390$4,390.83138$4,980.83
16 to 30 Days Past Due59$6,512.28192$9,965.28523$11,252.2857$4,542.7291$6,675.72139$7,265.72
31 to 60 Days Past Due56$1,383.16189$4,836.16520$6,123.1656$1,383.1690$3,516.16138$4,106.16
More Than 60 Days Past Due58$7,311.92191$10,764.92522$12,051.9258$7,311.9292$9,444.92140$10,034.92
16 to 30 Days to Due78$25,223.85211$28,676.85542$29,963.8567$27,102.52101$29,235.52149$29,825.52
31 to 60 Days to Due161$178,370.49294$181,823.49625$183,110.49127$93,458.73161$95,591.73209$96,181.73
 > 60 Days to Due568$1,189,098.92701$1,192,551.921032$1,193,838.92387$872,103.19421$874,236.19469$874,826.19

 

This is the results I am trying to get:

 

Report DateCategoryOwnershipSite - TotalSite - Sales
8/8/2022Within 7 Days of DueSite54$2,104.15
8/8/20228 to 15 Days to DueSite55$1,933.28
8/8/2022Up to 7 Days Past DueSite58$5,023.44
8/8/20228 to 15 Days Past DueSite54$2,149.30
8/8/202216 to 30 Days Past DueSite59$6,512.28
8/8/202231 to 60 Days Past DueSite56$1,383.16
8/8/2022More Than 60 Days Past DueSite58$7,311.92
8/8/202216 to 30 Days to DueSite78$25,223.85
8/8/202231 to 60 Days to DueSite161$178,370.49
8/8/2022 > 60 Days to DueSite568$1,189,098.92
8/8/2022Within 7 Days of DueRegion187$5,557.15
8/8/20228 to 15 Days to DueRegion188$5,386.28
8/8/2022Up to 7 Days Past DueRegion191$8,476.44
8/8/20228 to 15 Days Past DueRegion187$5,602.30
8/8/202216 to 30 Days Past DueRegion192$9,965.28
8/8/202231 to 60 Days Past DueRegion189$4,836.16
8/8/2022More Than 60 Days Past DueRegion191$10,764.92
8/8/202216 to 30 Days to DueRegion211$28,676.85
8/8/202231 to 60 Days to DueRegion294$181,823.49
8/8/2022 > 60 Days to DueRegion701$1,192,551.92
8/8/2022Within 7 Days of DueNational518$6,844.15
8/8/20228 to 15 Days to DueNational519$6,673.28
8/8/2022Up to 7 Days Past DueNational522$9,763.44
8/8/20228 to 15 Days Past DueNational518$6,889.30
8/8/202216 to 30 Days Past DueNational523$11,252.28
8/8/202231 to 60 Days Past DueNational520$6,123.16
8/8/2022More Than 60 Days Past DueNational522$12,051.92
8/8/202216 to 30 Days to DueNational542$29,963.85
8/8/202231 to 60 Days to DueNational625$183,110.49
8/8/2022 > 60 Days to DueNational1032$1,193,838.92
8/7/2022Within 7 Days of DueSite55$3,892.93
8/7/20228 to 15 Days to DueSite54$1,786.07
8/7/2022Up to 7 Days Past DueSite57$3,234.66
8/7/20228 to 15 Days Past DueSite56$2,257.83
8/7/202216 to 30 Days Past DueSite57$4,542.72
8/7/202231 to 60 Days Past DueSite56$1,383.16
8/7/2022More Than 60 Days Past DueSite58$7,311.92
8/7/202216 to 30 Days to DueSite67$27,102.52
8/7/202231 to 60 Days to DueSite127$93,458.73
8/7/2022 > 60 Days to DueSite387$872,103.19
8/7/2022Within 7 Days of DueRegion89$6,025.93
8/7/20228 to 15 Days to DueRegion88$3,919.07
8/7/2022Up to 7 Days Past DueRegion91$5,367.66
8/7/20228 to 15 Days Past DueRegion90$4,390.83
8/7/202216 to 30 Days Past DueRegion91$6,675.72
8/7/202231 to 60 Days Past DueRegion90$3,516.16
8/7/2022More Than 60 Days Past DueRegion92$9,444.92
8/7/202216 to 30 Days to DueRegion101$29,235.52
8/7/202231 to 60 Days to DueRegion161$95,591.73
8/7/2022 > 60 Days to DueRegion421$874,236.19
8/7/2022Within 7 Days of DueNational137$6,615.93
8/7/20228 to 15 Days to DueNational136$4,509.07
8/7/2022Up to 7 Days Past DueNational139$5,957.66
8/7/20228 to 15 Days Past DueNational138$4,980.83
8/7/202216 to 30 Days Past DueNational139$7,265.72
8/7/202231 to 60 Days Past DueNational138$4,106.16
8/7/2022More Than 60 Days Past DueNational140$10,034.92
8/7/202216 to 30 Days to DueNational149$29,825.52
8/7/202231 to 60 Days to DueNational209$96,181.73
8/7/2022 > 60 Days to DueNational469$874.82

 

I am attaching the input data on Excel and the Workflow I have close to what I am looking for. I credit @AngelosPachis for helping earlier today to resolve the two columns. But expanding this to more columns is a challenge and I just can't see to figure it out. Do I need to add yet another set of transpose and cross tabs?

 

1 REPLY 1
JPSeagull
8 - Asteroid

I figured it out with the help of @joelmiller66 on another posting. 

Labels
Top Solution Authors