Multi-level Pivot Table - Crosstab 2 column header not possible

[thuyduongnguyen]

Dear Alteryx community,

To create an output just like Pivot table in Excel (with 2 fields on Column), I have search many posts about Crosstab and Transpose to try out but haven't succeeded yet.

Crosstab tool is great but it doesn't support more than 1 column header.

Input:

Location	Year	Quarter	New	Close	Sum
A	2019	Q1	1	-1	0
A	2019	Q2	1	-1	0
A	2019	Q3	1	-1	0
A	2019	Q4	1	-1	0
B	2019	Q1	10	-1	9
B	2019	Q2	10	-1	9
B	2019	Q3	10	-1	9
B	2019	Q4	10	-1	9
A	2020	Q1	2	-1	1
A	2020	Q2	2	-1	1
A	2020	Q3	2	-1	1
A	2020	Q4	2	-1	1
B	2020	Q1	20	-1	19
B	2020	Q2	20	-1	19
B	2020	Q3	20	-1	19
B	2020	Q4	20	-1	19

Expected Output: (2 different tables can be saved in one Sheet).

I will use these table to create charts in Excel for each location, so this format need to be kept this way.

	2019	2019	2019	2019	2020	2020	2020	2020
A	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4
New	1	1	1	1	2	2	2	2
Close	-1	-1	-1	-1	-1	-1	-1	-1
Sum	0	0	0	0	1	1	1	1

	2019	2019	2019	2019	2020	2020	2020	2020
B	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4
New	10	10	10	10	20	20	20	20
Close	-1	-1	-1	-1	-1	-1	-1	-1
Sum	9	9	9	9	19	19	19	19

I appreciate every suggestion or work-around solution for this to work.

Thank you very much and Best Regards,

Duong 

[AngelosPachis · Accepted solution]

Hi @thuyduongnguyen ,

I've mocked up a workflow for you that I think achieves what you are looking for. The output is an excel file that looks something like that

and it's in the same sheet of an excel file.

The workflow is this:

which transposes and cross-tabs the data till it brings them to the required format. Then I have created a separate field, which assigns a row ID to maintain the sequence that your records will appear (Quarter>New>Close>Sum)

Finally, to force both tables in the same excel sheet in the desired format, you have to play with the reporting tools, or just use an output data tool.

Hope that helps,

Regards,

Angelos

[thuyduongnguyen]

Thank you very much for your suggestion!

I have tried and it works perfect for my case.

Only a small modification:

I also choose [Year] in Data Columns of Transpose tool, so in the end I can use Output Data tool instead of manual adjusting column name if using Basic Table tool.