Transpose/Cross Tab Question

[BrianDeg]

Hello! I am looking for some help with what combination of transpose and cross tabs I would need to take the input data and transform to the output data. I also understand there may be an issue with duplicate column headers... is this something that can be handled within alteryx? Would of course appreciate a solution that can work for multiple years of data. Appreciate your help in advance. 

Input:

Name	Company #	Sales	Costs	Taxes	Sales	Costs	Taxes
Year		2018	2018	2018	2019	2019	2019
Company 1	123	150	100	8	165	110	8
Company 2	456	225	175	11	248	193	12
Company 3	789	343	212	17	377	233	19

Desired Output:

Year	Name	Company #	Account Name	Value
2018	Company 1	123	Sales	150
2018	Company 1	123	Costs	100
2018	Company 1	123	Taxes	8
2018	Company 2	456	Sales	225
2018	Company 2	456	Costs	175
2018	Company 2	456	Taxes	11
2018	Company 3	789	Sales	343
2018	Company 3	789	Costs	212
2018	Company 3	789	Taxes	17
2019	Company 1	123	Sales	165
2019	Company 1	123	Costs	110
2019	Company 1	123	Taxes	8
2019	Company 2	456	Sales	248
2019	Company 2	456	Costs	193
2019	Company 2	456	Taxes	12
2019	Company 3	789	Sales	377
2019	Company 3	789	Costs	233
2019	Company 3	789	Taxes	19

[Aaron_Harter · Accepted solution]

Hi @BrianDeg, 

I would recommend reading in the data with generic field headers, then splitting the data into separate streams to isolate the header values before transposing and joining back together into your desired format:

  

[SamYX]