Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Crosstab with two labels

CZSE
5 - Atom

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

RegionCustomerProductSalesQuarter
EastCustomer 1Product A232017 Q1
EastCustomer 2Product A2143212017 Q2
EastCustomer 3Product A342017 Q3
Eastcustomer 4Product A1232017 Q4
EastCustomer 5Product A3452018 Q1
EastCustomer 6Product A12432018 Q1
EastCustomer 7Product A3452018 Q1
EastCustomer 1Product A13242018 Q1
EastCustomer 2Product A3452019 Q1
EastCustomer 3Product A4572019 Q2
Eastcustomer 4Product A78092019 Q3
EastCustomer 5Product A682019 Q4
EastCustomer 6Product B8742017 Q1
EastCustomer 7Product B79752017 Q2
EastCustomer 1Product B6842017 Q3
EastCustomer 2Product B672017 Q4
EastCustomer 3Product B45652018 Q1
Eastcustomer 4Product B786572018 Q1
EastCustomer 5Product B452018 Q1
EastCustomer 6Product B762018 Q1
EastCustomer 7Product B452019 Q1
EastCustomer 1Product B768792019 Q2
EastCustomer 2Product B7892019 Q3
EastCustomer 3Product B652019 Q4
Westcustomer 4Product A87982017 Q1
WestCustomer 5Product A346782017 Q2
WestCustomer 6Product A34652017 Q3
WestCustomer 7Product A76982017 Q4
WestCustomer 1Product A4352018 Q1
WestCustomer 2Product A7682018 Q1
WestCustomer 3Product A2352018 Q1
Westcustomer 4Product A5682018 Q1
WestCustomer 5Product A7682019 Q1
WestCustomer 6Product A102019 Q2
WestCustomer 7Product A111202019 Q3
WestCustomer 1Product A3202019 Q4
WestCustomer 2Product B1322017 Q1
WestCustomer 3Product B2342017 Q2
Westcustomer 4Product B2342017 Q3
WestCustomer 5Product B4562017 Q4
WestCustomer 6Product B652018 Q1
WestCustomer 7Product B8792018 Q1
WestCustomer 1Product B12018 Q1
WestCustomer 2Product B762018 Q1
WestCustomer 3Product B452019 Q1
Westcustomer 4Product B52019 Q2
WestCustomer 5Product B657652019 Q3
WestCustomer 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 B
Customer 1East23213429345124323132462457780912345678910112321
Customer 2East874797568467456578657457809688747975111267943898134567906743
Customer 3East17251592913341058785156071671429474129181412122131148110189188112416911165
Customer 4East25762388319841431300523348589207798017088307313219519119122802311718124815587
Customer 5East3427318372634181172253108991112726486212584734142259241571437128153238327199109
Customer 6East4278397913284219214453883131333374992254286395152323291951646233189295406243131
Customer 7East5129477453934257256654657271554023498295988056162387342331855338225352485287153
Customer 1West59805569945842952988554314117746719104337689717172451392712064443261409564331175
Customer 2West68316365352343333410562055519953204110379391378182515443092273548297466643375197
Customer 3West76827160758843713832569796922159689116421093039192579493472482653333523722419219
Customer 4West8533795616534409425457753832436617412246279469101102643543852691758369580801463241
Customer 5West93848751571844474676585279726572659128504496351111127075942328100863405637880507263
Customer 6West102359546978344855098593021128779144134546198011211227716446130109968441694959551285
Customer 7West110861034238484523552051007625309856291405878996713113283569499321190734777511038595307
4 REPLIES 4
atcodedog05
21 - Polaris

Hi @CZSE ,

 

Here is a workflow for the task.

 

Input: table provided by you

 

Output

atcodedog05_0-1601521800495.png

 

Workflow

atcodedog05_1-1601521887348.png

 

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

atcodedog05_2-1601521952419.png

 

Hope this helps : )

 

If this helps please mark the post as solution.

 

grazitti_sapna
15 - Aurora
15 - Aurora

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.

 

grazitti_sapna_0-1601526248051.png

 

 

Thanks.

Sapna Gupta
CZSE
5 - Atom

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.

atcodedog05
21 - Polaris

Happy to help : ) @CZSE 

 

Cheers and Happy Analysing 🥳🥳

Labels