Transpose a Hyperion report

[ck2019]

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.36	1980_USD.36	1989.36	1989.36	1989.36	1989.36	1989.CO	1993.No Country
	699_Data Eliminations and allocations	699_Data Eliminations and allocations	140_Regional corporate costs	100_Country corporate costs	480_DA GBL Recharge	600_Consumer Information core	100_Country corporate costs	600_Consumer Information core
TOTREVEN_Total revenue	5,000	6,000	5,000	6,000	5,000	6,000	5,000	6,000
EBITXXXX_Earnings Before Interest and Tax	20,000	21,000	20,000	21,000	20,000	21,000	20,000	21,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 & Country	Company	BU	TOTREVEN_Total revenue	EBITXXXX_Earnings Before Interest and Tax
1980_GBP.36	1980	699_Data Eliminations and allocations	5000	20000
1980_USD.36	1980	699_Data Eliminations and allocations	6000	21000
1989.36	1989	140_Regional corporate costs	5000	20000
1989.36	1989	100_Country corporate costs	6000	21000
1989.36	1989	480_DA GBL Recharge	5000	20000
1989.36	1989	600_Consumer Information core	6000	21000
1989.CO	1989	100_Country corporate costs	5000	20000
1993.No Country	1993	600_Consumer Information core	6000	21000

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

Company		New Total Rev	New EBIT
1980	699_Data Eliminations and allocations	11000	41000
1989	140_Regional corporate costs	5000	20000
1989	100_Country corporate costs	11000	41000
1989	480_DA GBL Recharge	5000	20000
1989	600_Consumer Information core	6000	21000
1993	600_Consumer Information core	6000	21000

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

[atcodedog05 · Accepted solution]

Hi @ck2019 

Here is how you can do it.

Workflow:

Edit: updated solution for using input file.

Hope this helps : )

[mst3k · Accepted solution]

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.

[ck2019]

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

[atcodedog05]

Happy to help : ) @ck2019 

Cheers and have a nice day!

[mst3k]

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