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

Transpose a Hyperion report

ck2019
9 - Comet

Hey, I'm struggling with this so wondering if anyone can guide me

 

I have multiple tables across multiple sheets, and I end up with this in Excel

 

 1980_GBP.361980_USD.361989.361989.361989.361989.361989.CO1993.No Country
 699_Data Eliminations and allocations699_Data Eliminations and allocations140_Regional corporate costs100_Country corporate costs480_DA GBL Recharge600_Consumer Information core100_Country corporate costs600_Consumer Information core
                    TOTREVEN_Total revenue5,0006,0005,0006,0005,0006,0005,0006,000
               EBITXXXX_Earnings Before Interest and Tax20,00021,00020,00021,00020,00021,00020,00021,000

 

If I were to transpose this in Excel, then I would copy and paste it elsewhere using the transpose function to end up with this (creating Company column based on first 4 characters of Company & Country column) that I have added headers for

 

Company & CountryCompanyBU                    TOTREVEN_Total revenue               EBITXXXX_Earnings Before Interest and Tax
1980_GBP.361980699_Data Eliminations and allocations500020000
1980_USD.361980699_Data Eliminations and allocations600021000
1989.361989140_Regional corporate costs500020000
1989.361989100_Country corporate costs600021000
1989.361989480_DA GBL Recharge500020000
1989.361989600_Consumer Information core600021000
1989.CO1989100_Country corporate costs500020000
1993.No Country1993600_Consumer Information core600021000

 

I would then create a sumifs formula and remove duplicates of company to end up with a summary table like this

 

Company New Total RevNew EBIT
1980699_Data Eliminations and allocations1100041000
1989140_Regional corporate costs500020000
1989100_Country corporate costs1100041000
1989480_DA GBL Recharge500020000
1989600_Consumer Information core600021000
1993600_Consumer Information core600021000

 

And I just can't replicate that in Alteryx and would love to avoid having to create a macro to achieve the same, so wondered if anyone had any suggestions on how to achieve this please? Thanks a lot

 

 

 

5 REPLIES 5
atcodedog05
22 - Nova
22 - Nova

Hi @ck2019 

 

Here is how you can do it.

Workflow:

atcodedog05_0-1632116716677.png

 

Edit: updated solution for using input file.

 

Hope this helps : )

mst3k
11 - Bolide

You need to rely on the transpose tool, and ironically I crosstabbed it back into the shape you wanted it in. The summarize tool appears to do what you need in the last step.

 

mst3k_0-1632116824790.png

 

ck2019
9 - Comet

Brilliant, thanks both.  The tile tool is a new one for me but understand its worth here, thanks a lot!

atcodedog05
22 - Nova
22 - Nova

Happy to help : ) @ck2019 

Cheers and have a nice day!

mst3k
11 - Bolide

The "unique value" configuration on the tile tool can act like a record ID tool within subgroups. Like restarting the record ID for each new value of field XYZ

Labels