Pivot Table (Multi-Fields)

Question

Hi SME,

I would like to create a pivot table similar to Excel in Alteryx as shown in the attached file.  The Source tab is the raw data and the Desired Output tab is the pivot table.

Could you please help me figure out a way to do this?  I can do it if I only have one number field. For some reasons, I'm not able to get the two number fields to work.

Thanks,

kwl

Pivot Example.xlsx

binu_acs · Accepted Answer

@knnwndlm the below weekly challenge gives you an idea about the pivot part in Alteryx

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-292-It-s-about-Pivoting-Prepping-and-Reporting/td-p/841274

ShankerV · Accepted Answer

Hi @knnwndlm

I believe this is very similar to the solution already provided in another thread of yours.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Data-Summary/td-p/1050871

While creating the below using summarize tool also add the IA column in addtion.

Many thanks

Shanker V

knnwndlm · Accepted Answer

Hi @ShankerV!

Yes, it's very similar, but I'm unable to get it to work properly.  I did use the Summarize Tool for both IA and State and then Crosstab Tool for the State.  This is where I get stuck with using the Crosstab tool again for IA.  Clearly, I need to use IA in the Values for New Columns, but I'm not sure what to put under the Change Column Headers.

Thanks,

kwl

ShankerV · Accepted Answer

Hi @knnwndlm

Please find the expected output. From the below you can use the previous workflow to integrate and get the result.

If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.

Many thanks

Shanker V

ShankerV · Accepted Answer

Hi @knnwndlm

Step 1: Input.

Step 2: filter tool

Step 3: dynamic tool

Step 4: Dynamic tool

Step 5: Join tool

If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.

Many thanks

Shanker V

ShankerV · Accepted Answer

Hi @knnwndlm

Step 6: Union tool

Step 7: Select tool

Step 8: Formula tool

tonumber([Arizona Activities])+tonumber([California Activities])

tonumber([Arizona IA])+tonumber([California IA])

Output:

If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.

Many thanks

Shanker V

ShankerV · Accepted Answer

Hi @knnwndlm

Please find the final output of the Pivot table

Joined with the output and got the result.

If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.

Many thanks

Shanker V

ShankerV · Accepted Answer

Hi @knnwndlm

Sure, give me sometime, unable to save the workflow due to below error.

Will get it sorted now.

Many thanks

Shanker V

knnwndlm · Accepted Answer

Thank you @ShankerV !  Able to follow the first half but stuck on the second half without the actual workflow.

ShankerV · Accepted Answer

Hi @knnwndlm

Few minutes please, getting the issue fixed.

did many changes in the summarize tool and union tool.

Many thanks

Shanker v

ShankerV · Accepted Answer

Hi @knnwndlm

The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V

knnwndlm · Accepted Answer

Thank you so much for your help @ShankerV!  Have a wonderful day!