Hi, I am trying to create a pivot table similar to excel by using cross tab on 2 fields at the same time. My source data is something similar to the below AccountField 1Field 2Sum_ Amount502009170CIR0502009170URO0502009367099905020093670LCS934649051000710BRI14874.1451000710CER19840.1651000710HYD-1403.3651000710PIR3919.7 and the desired format is the following 1010101017017036703670AccountBRICERHYDPIRCIRURO999LCS502009 000934649051000714874.1419840.16-1403.363919.7 I essentially want the top row of my data table to be repeated/group by "Field 1" and "Field 2" respectively. On Excel I can achieve this by choosing "Field 1" and "Field 2" as "Columns" in a pivot table but I cannot figure out what is the process for Alteryx? Any help will be much appreciated!

Cross tab with multiple fields

Hi,

I am trying to create a pivot table similar to excel by using cross tab on 2 fields at the same time. My source data is something similar to the below

Account	Field 1	Field 2	Sum_ Amount
502009	170	CIR	0
502009	170	URO	0
502009	3670	999	0
502009	3670	LCS	9346490
510007	10	BRI	14874.14
510007	10	CER	19840.16
510007	10	HYD	-1403.36
510007	10	PIR	3919.7

and the desired format is the following

	10	10	10	10	170	170	3670	3670
Account	BRI	CER	HYD	PIR	CIR	URO	999	LCS
502009					0	0	0	9346490
510007	14874.14	19840.16	-1403.36	3919.7

I essentially want the top row of my data table to be repeated/group by "Field 1" and "Field 2" respectively. On Excel I can achieve this by choosing "Field 1" and "Field 2" as "Columns" in a pivot table but I cannot figure out what is the process for Alteryx?

Any help will be much appreciated!

Workflow

Transformation

Help

Preparation

Accepted answers

RolandSchubert

Hi @YanishDewan ,

the Cross Tab tool works on one field at a time, so you will need two Cross Tab tools (first one to cross tab SUM_Amount, second one for Field 2). Union the results and use a Dynamic Rename tool to change the headers. But - there is one problem. Headers have to be different for different fields, so the four columns label "10" will be "10", "10_2", "10_3", "10_4". I've attached a sample workflow, hope this helps.

Best regards

Roland

CrossTabMultiple.yxmd

All comments

RolandSchubert

Hi @YanishDewan ,

Best regards

Roland

CrossTabMultiple.yxmd

YanishDewan

Thanks @RolandSchubert This is kind of what I needed. I had a look at the table tool which has a pivot table functionality in Alteryx (https://help.alteryx.com/2018.2/PortfolioComposerTable.htm). Looking at the "Beginning data example" and the "Example Output" for the "Methodology over Variable", this looks more similar to what I want as a final output.

Would you be able to advise how the example output was achieved for the data example?

cyeager

I struggle with this as well. One idea is to concatenate Field 1 & 2 in a Formula node ( i.e. [Field 1] + "-" + [Field 2] prior to the Cross-Tab, and then use as your column header. You may have to change Field 1 to a V_String Type for it to concatenate. It still isn't exactly what you'd like, but it gets you closer. 🙂

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703