Hi Alteryx Community, I am trying to format data into a cross tab, but would like to have two labels as the first two rows. It is very similar to a pivot table in excel, but have not been able to figure it out in Alteryx. Input Data RegionCustomerProductSalesQuarterEastCustomer 1Product A232017 Q1EastCustomer 2Product A2143212017 Q2EastCustomer 3Product A342017 Q3Eastcustomer 4Product A1232017 Q4EastCustomer 5Product A3452018 Q1EastCustomer 6Product A12432018 Q1EastCustomer 7Product A3452018 Q1EastCustomer 1Product A13242018 Q1EastCustomer 2Product A3452019 Q1EastCustomer 3Product A4572019 Q2Eastcustomer 4Product A78092019 Q3EastCustomer 5Product A682019 Q4EastCustomer 6Product B8742017 Q1EastCustomer 7Product B79752017 Q2EastCustomer 1Product B6842017 Q3EastCustomer 2Product B672017 Q4EastCustomer 3Product B45652018 Q1Eastcustomer 4Product B786572018 Q1EastCustomer 5Product B452018 Q1EastCustomer 6Product B762018 Q1EastCustomer 7Product B452019 Q1EastCustomer 1Product B768792019 Q2EastCustomer 2Product B7892019 Q3EastCustomer 3Product B652019 Q4Westcustomer 4Product A87982017 Q1WestCustomer 5Product A346782017 Q2WestCustomer 6Product A34652017 Q3WestCustomer 7Product A76982017 Q4WestCustomer 1Product A4352018 Q1WestCustomer 2Product A7682018 Q1WestCustomer 3Product A2352018 Q1Westcustomer 4Product A5682018 Q1WestCustomer 5Product A7682019 Q1WestCustomer 6Product A102019 Q2WestCustomer 7Product A111202019 Q3WestCustomer 1Product A3202019 Q4WestCustomer 2Product B1322017 Q1WestCustomer 3Product B2342017 Q2Westcustomer 4Product B2342017 Q3WestCustomer 5Product B4562017 Q4WestCustomer 6Product B652018 Q1WestCustomer 7Product B8792018 Q1WestCustomer 1Product B12018 Q1WestCustomer 2Product B762018 Q1WestCustomer 3Product B452019 Q1Westcustomer 4Product B52019 Q2WestCustomer 5Product B657652019 Q3WestCustomer 6Product B432019 Q4 Output Data 2017 Q12017 Q12017 Q22017 Q22017 Q32017 Q32017 Q42017 Q42018 Q12018 Q12018 Q22018 Q22018 Q32018 Q32018 Q42018 Q42019 Q12019 Q12019 Q22019 Q22019 Q32019 Q32019 Q42019 Q4 Product AProduct BProduct AProduct BProduct AProduct BProduct AProduct BProduct AProduct BProduct AProduct BProduct AProduct BProduct AProduct BProduct AProduct BProduct AProduct BProduct AProduct BProduct AProduct BCustomer 1East23213429345124323132462457780912345678910112321Customer 2East874797568467456578657457809688747975111267943898134567906743Customer 3East17251592913341058785156071671429474129181412122131148110189188112416911165Customer 4East25762388319841431300523348589207798017088307313219519119122802311718124815587Customer 5East3427318372634181172253108991112726486212584734142259241571437128153238327199109Customer 6East4278397913284219214453883131333374992254286395152323291951646233189295406243131Customer 7East5129477453934257256654657271554023498295988056162387342331855338225352485287153Customer 1West59805569945842952988554314117746719104337689717172451392712064443261409564331175Customer 2West68316365352343333410562055519953204110379391378182515443092273548297466643375197Customer 3West76827160758843713832569796922159689116421093039192579493472482653333523722419219Customer 4West8533795616534409425457753832436617412246279469101102643543852691758369580801463241Customer 5West93848751571844474676585279726572659128504496351111127075942328100863405637880507263Customer 6West102359546978344855098593021128779144134546198011211227716446130109968441694959551285Customer 7West110861034238484523552051007625309856291405878996713113283569499321190734777511038595307

Crosstab with two labels

Hi Alteryx Community,

I am trying to format data into a cross tab, but would like to have two labels as the first two rows.

It is very similar to a pivot table in excel, but have not been able to figure it out in Alteryx.

Input Data

Region	Customer	Product	Sales	Quarter
East	Customer 1	Product A	23	2017 Q1
East	Customer 2	Product A	214321	2017 Q2
East	Customer 3	Product A	34	2017 Q3
East	customer 4	Product A	123	2017 Q4
East	Customer 5	Product A	345	2018 Q1
East	Customer 6	Product A	1243	2018 Q1
East	Customer 7	Product A	345	2018 Q1
East	Customer 1	Product A	1324	2018 Q1
East	Customer 2	Product A	345	2019 Q1
East	Customer 3	Product A	457	2019 Q2
East	customer 4	Product A	7809	2019 Q3
East	Customer 5	Product A	68	2019 Q4
East	Customer 6	Product B	874	2017 Q1
East	Customer 7	Product B	7975	2017 Q2
East	Customer 1	Product B	684	2017 Q3
East	Customer 2	Product B	67	2017 Q4
East	Customer 3	Product B	4565	2018 Q1
East	customer 4	Product B	78657	2018 Q1
East	Customer 5	Product B	45	2018 Q1
East	Customer 6	Product B	76	2018 Q1
East	Customer 7	Product B	45	2019 Q1
East	Customer 1	Product B	76879	2019 Q2
East	Customer 2	Product B	789	2019 Q3
East	Customer 3	Product B	65	2019 Q4
West	customer 4	Product A	8798	2017 Q1
West	Customer 5	Product A	34678	2017 Q2
West	Customer 6	Product A	3465	2017 Q3
West	Customer 7	Product A	7698	2017 Q4
West	Customer 1	Product A	435	2018 Q1
West	Customer 2	Product A	768	2018 Q1
West	Customer 3	Product A	235	2018 Q1
West	customer 4	Product A	568	2018 Q1
West	Customer 5	Product A	768	2019 Q1
West	Customer 6	Product A	10	2019 Q2
West	Customer 7	Product A	11120	2019 Q3
West	Customer 1	Product A	320	2019 Q4
West	Customer 2	Product B	132	2017 Q1
West	Customer 3	Product B	234	2017 Q2
West	customer 4	Product B	234	2017 Q3
West	Customer 5	Product B	456	2017 Q4
West	Customer 6	Product B	65	2018 Q1
West	Customer 7	Product B	879	2018 Q1
West	Customer 1	Product B	1	2018 Q1
West	Customer 2	Product B	76	2018 Q1
West	Customer 3	Product B	45	2019 Q1
West	customer 4	Product B	5	2019 Q2
West	Customer 5	Product B	65765	2019 Q3
West	Customer 6	Product B	43	2019 Q4

Output Data

		2017 Q1	2017 Q1	2017 Q2	2017 Q2	2017 Q3	2017 Q3	2017 Q4	2017 Q4	2018 Q1	2018 Q1	2018 Q2	2018 Q2	2018 Q3	2018 Q3	2018 Q4	2018 Q4	2019 Q1	2019 Q1	2019 Q2	2019 Q2	2019 Q3	2019 Q3	2019 Q4	2019 Q4
		Product A	Product B	Product A	Product B	Product A	Product B	Product A	Product B	Product A	Product B	Product A	Product B	Product A	Product B	Product A	Product B	Product A	Product B	Product A	Product B	Product A	Product B	Product A	Product B
Customer 1	East	23	21	34	29	345	1243	23	1324	62	457	7809	1	2	3	4	5	6	7	8	9	10	11	23	21
Customer 2	East	874	7975	684	67	4565	78657	45	7809	68	874	7975	11	12	67	9	43	8	98	13	45	67	90	67	43
Customer 3	East	1725	15929	1334	105	8785	156071	67	14294	74	1291	8141	21	22	131	14	81	10	189	18	81	124	169	111	65
Customer 4	East	2576	23883	1984	143	13005	233485	89	20779	80	1708	8307	31	32	195	19	119	12	280	23	117	181	248	155	87
Customer 5	East	3427	31837	2634	181	17225	310899	111	27264	86	2125	8473	41	42	259	24	157	14	371	28	153	238	327	199	109
Customer 6	East	4278	39791	3284	219	21445	388313	133	33749	92	2542	8639	51	52	323	29	195	16	462	33	189	295	406	243	131
Customer 7	East	5129	47745	3934	257	25665	465727	155	40234	98	2959	8805	61	62	387	34	233	18	553	38	225	352	485	287	153
Customer 1	West	5980	55699	4584	295	29885	543141	177	46719	104	3376	8971	71	72	451	39	271	20	644	43	261	409	564	331	175
Customer 2	West	6831	63653	5234	333	34105	620555	199	53204	110	3793	9137	81	82	515	44	309	22	735	48	297	466	643	375	197
Customer 3	West	7682	71607	5884	371	38325	697969	221	59689	116	4210	9303	91	92	579	49	347	24	826	53	333	523	722	419	219
Customer 4	West	8533	79561	6534	409	42545	775383	243	66174	122	4627	9469	101	102	643	54	385	26	917	58	369	580	801	463	241
Customer 5	West	9384	87515	7184	447	46765	852797	265	72659	128	5044	9635	111	112	707	59	423	28	1008	63	405	637	880	507	263
Customer 6	West	10235	95469	7834	485	50985	930211	287	79144	134	5461	9801	121	122	771	64	461	30	1099	68	441	694	959	551	285
Customer 7	West	11086	103423	8484	523	55205	1007625	309	85629	140	5878	9967	131	132	835	69	499	32	1190	73	477	751	1038	595	307

Best Practices

Accepted answers

atcodedog05

Hi @CZSE ,

Here is a workflow for the task.

Input: table provided by you

Output

Workflow

Make sure your output tool is configured to Not output column name since they are 1,2,3 ...

Make sure to uncheck the highlighted option

Hope this helps : )

If this helps please mark the post as solution.

Format-Q.yxmd

All comments

atcodedog05

Hi @CZSE ,

Here is a workflow for the task.

Input: table provided by you

Output

Workflow

Make sure your output tool is configured to Not output column name since they are 1,2,3 ...

Make sure to uncheck the highlighted option

Hope this helps : )

If this helps please mark the post as solution.

Format-Q.yxmd

grazitti_sapna

Hi @CZSE , I have prepared a workflow but your output is totally different from what you have provided in input and my output matches the input, please have a look.

Thanks.

CrossTab-GS.yxmd

CZSE

Thank you atcodedog05! this is what I was looking for.

I just needed to use a select tool at the end to put the customer in the first column and the Region in the second column.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

Aguisande 317