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 |
Solved! Go to Solution.
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.
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.
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.