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 | - | - | - | - |
Solved! Go to Solution.
One Way of Doing this. Please Check.
If it works Kindly accept it as Solution.
Thanks
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:
Input Data: Drag and drop an Input Data tool to load your data into Alteryx.
Cross Tab Tool Configuration:
Filter Rows with Zero Count:
Optional: Rename Columns:
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.
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.
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
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
Hi Pang_Hee_Choy,
Thanks for the solution. This worked as expected and fulfilled the requirement.
Regards,
Suhas