Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Conversion from Normal Excel Sheet to a Pivot Table

shaantrika
6 - Meteoroid

Hi there,

 

Hope you are doing well.

 

I am making this post today as I am trying to figure out how to implement pivot tables within Alteryx, but am unable to understand which combination of tools/their configurations I should be using for my specific purpose.

 

To outline the issue that I would like to solve, I have attached two spreadsheets as a smaller sample.

 

I would like to pivot off of the spreadsheet labeled "Tasks and Roles" into a pivot table that resembles what I have in the spreadsheet labeled "Records_Pivot", just in pivot form.

 

If anyone could provide a sample workflow that could be used to reach my end goal of portraying what is in "Records_Pivot" in pivot table form, pivoting off of "Tasks and Roles", that would be very helpful and much appreciated.

 

Best regards,

Shaan

4 REPLIES 4
binuacs
20 - Arcturus

@shaantrika One way of doing this. The PIVOT functionality can be achieved by using the combination of Transpose/Tile/Cross Tab tools. The Record ID tool is used to retain the same order of the Task field. The Multi-Field formula just updates the cross tab output to 'X'

 

you can go through the weekly challenge which, will give you more idea about pivoting

 

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-292-It-s-about-Pivoting-Prepping-and-Rep...

binuacs_1-1661465549148.png

 

 

SPetrie
12 - Quasar

You may be able to do it with just a crosstab and a formula tool to put in the "X" value.

SPetrie_0-1661465667316.png

 

shaantrika
6 - Meteoroid

Thank you both for your prompt replies. I have just now got the chance to read your responses and give this a shot.

 

@binuacs, I have read your solution and it seems to make sense. I have also read @SPetrie's reply and have chosen to try this shorter method out first. Even if I am able to get this to work I will still try your solution to get a better idea of all the tools working in conjunction, couldn't hurt.

 

@SPetrie, could you click on the formula tool so I can see how you configured it? Is "Value" that you assigned to "X" a separate output column that coordinates in with the roles matching to the tasks by way of the cross-tab tool? A little confused as to how this is working based on the formula tool.

 

Thank you,

Shaan Trika

SPetrie
12 - Quasar

Its just the letter X in a new column called Value to create the values to fill in the cross tab. Could be numeric or something else, I just put X to match your example.

SPetrie_0-1661968471045.png

 

 

Labels