Creating PIVOT in Alteryx

[Suhastm08]

Hi All,

I am fairly new to Alteryx and trying to create a PIVOT using Alteryx. Please refer to the below sample data, i have the Customer ID, Customer Category and Age Bucket for pending Customer Queries.

I want to create a PIVOT table which reflects the queries pending for each customer category under the age bucket. I am not able to upload screenshots or Input files due to data sharing restrictions. The Customer Category and Age Bucket should remain same even if there are no aged queries. For Example, there are no queries under 'Diamond' category, the output should reflect 'Diamond' category with no items under the age buckets.

I have tried using the Summarise and Cross Tab tool but unable to achieve the required output. Can you please suggest a solution? 

Input Data:

Customer ID	Customer Category	Age Bucket
B12	Gold	0
B13	Silver	1
B14	Platinum	2
B15	Bronze	>2
B16	Bronze	>2
B17	Gold	2
B18	Bronze	1
B19	Gold	2
B20	Platinum	1
B21	Bronze	0
B22	Bronze	0
B23	Silver	1
B24	Silver	>2
B25	Bronze	>2

Expected Output PIVOT:

Customer Category	Age 0	Age 1	Age 2	Age >2
Gold	1	-	2	-
Silver	-	2	-	1
Platinum	-	1	1	-
Bronze	2	1	-	3
Diamond	-	-	-	-

[cjaneczko]

Try this. Null values can be replaced with a multifield formula if required.

[grazitti_sapna]

Hi@Suhastm08 

One Way of Doing this. Please Check.

If it works Kindly accept it as Solution.

Thanks

[Hammad_Rashid]

To achieve the desired output in Alteryx, you can use the Cross Tab tool along with some additional configurations. Here are the steps to create the pivot table:

1. Input Data: Drag and drop an Input Data tool to load your data into Alteryx.

2. Cross Tab Tool Configuration:

   • Drag and drop a Cross Tab tool.
   • Connect the Input Data tool to the Cross Tab tool.
   • In the Cross Tab tool configuration, set the following fields:
     • Data Field: Customer ID
     • Column Field: Age Bucket
     • Row Field: Customer Category
     • Cell Value Field: Count (or any other summary method you prefer, depending on your needs)

3. Filter Rows with Zero Count:

   • After the Cross Tab tool, use a Filter tool to filter out rows where the Count is 0. This is to ensure that even if there are no queries for a specific combination of Customer Category and Age Bucket, it will still be included in the output.

4. Optional: Rename Columns:

   • You may want to use a Select tool to rename the columns if needed. This step is optional.

5. Output Data: Finally, use an Output Data tool to save or preview your results.

Now, when you run the workflow, you should get the desired pivot table as output.

The key is to use the Cross Tab tool with the appropriate configuration and filter out rows with zero counts to include all combinations of Customer Category and Age Bucket in the output.

[Pang_Hee_Choy · Accepted solution]

to add field that no in data, we can use Text Input for any require fixed table structure.

by create a table of Text input as below, and union into the data. the result will show the diamond and age bucket if not exist in data. 

the pair of customer category and age bucket is not important, it just 2 different list in a table.

the count can be either 0 or null. I preferred null as the end result is null instead of zero. 

[Suhastm08]

Hi cjaneczko,

Thanks for the suggested Solution, but this approach would still not capture 'Diamond' Customer Category as there are no pending queries in the data shared.

Thanks,

Suhas

[Suhastm08]

Hi Sapna Gupta,

Thanks for the suggested Solution, but this approach would still not capture 'Diamond' Customer Category as there are no pending queries in the data shared.

Thanks,

Suhas

[Suhastm08]

Hi Pang_Hee_Choy,

Thanks for the solution. This worked as expected and fulfilled the requirement.

Regards,

Suhas