Cross tab with multiple fields

[YanishDewan]

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!

[RolandSchubert · Accepted solution]

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

[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?

[fmvizcaino]

Hi @YanishDewan ,

Unfortunately, it is not possible to achieve the result you want using the pivot table option.

I'm attaching 2 examples: one showing how to accomplish the configuration of the table tool example and one using your data.

From help: 'Pivot Style: Select the style for displaying the data in the table. This option is only available when using data from a Cross Tab tool'

Best,

Fernando Vizcaino

[RolandSchubert]

Hi @YanishDewan ,

as @fmvizcaino already explained,  the pivot table functionality works with cross tab data only (that's what you have), but it does not provide an option to "group" data the way you want.

But you can use the table tool to create the result you want by adjusting the headers (it's not very flexible ...). Is this an option?

Best regards

Roland

[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.  🙂