Pivot Table (Multi-Fields)

[knnwndlm]

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

[binuacs · Accepted solution]

@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-Rep...

[ShankerV · Accepted solution]

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 solution]

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 solution]

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 solution]

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 solution]

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 solution]

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

[knnwndlm]

Hi @ShankerV 

Thank you for your help.  I'm still unable to follow it through completely.  Would it be possible for you to put it in a workflow?  

Many thanks,

Konn

[ShankerV · Accepted solution]

Hi @knnwndlm 

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

Will get it sorted now.

Many thanks

Shanker V