Pivot Table in Alteryx

[SH2023]

Hello,

I am new to Alteryx and suggested by Alteryx Community email to post a question on this Community Home Page "Ask Questions". Hope, this is the right place if not, please inform me.

I tried to design a workflow for data to have an output only showing how much for each Location Name having total for each Natural Account showing in the "Data" tab. (In "Data" tab, Natural Account is in 1 column. Need to be separated for different column for each natural account in the Output). I used Pivot Table Excel function and show the result/Output on the "Pivot" tab on this attached file here.

I tried to follow a sample posted in Alteryx Community, but I can't get the result as Excel Pivot Table. The Alteryx workflow shows "Issue" that I can't figure out what should be done.

Attached files: Data with Excel Pivot Table and Alteryx Workflow I created along with the Alteryx Output.

I appreciate any response.

Regards,

SH2023

[JamesCharnley · Accepted solution]

Hi @SH2023 

If we want one row per location_name and one column for each natural account, we can crosstab choosing to Group By location_name, with Natural Account as our headers and amount for our values. I've also added a filter to remove the total row that would skew the values.

I've used a CreW Macro called 'Add Totals' to save myself the effort of having to make those calculations myself, you can learn more about them here: http://www.chaosreignswithin.com/p/macros.html

This doesn't match the excel pivot exactly but you could brute force headers in if you really needed it for whatever reason.

[SH2023]

Hi James,

Thank you for the help. 

It seems the CreW Macro is different tool based on the link you sent.

Is the Crew Macro tool embeded to Alteryx once you install it?

If not, how did you put "Add Totals" in the workflow canvas?

If yes, will CreW Macro be added to Alteryx menu (f.e Join, Reporting, etc on the top bar)?

[JamesCharnley]

Hi @SH2023 

The CReW Macros are a pack of Alterx macros made by Adam Riley and @MarqueeCrew. Add Totals is just one of the useful tools within the pack. The installation instructions found on the website I linked can be followed to download them like so:

Once you've downloaded the file, extracted it to a location and run the install, you can point to the location from within User Settings -> Advanced User Settings -> Macros to add an extra palette of tools above the canvas, like so:

Once you have the tools downloaded, you can also open them up and have a look inside if you're curious how the total column and row can be made by aggregating and joining. 

[SH2023]

Hello James,

Again, thank you very much for more explanation. I have to get an approval by the company's IT department for downloading any external software.

Hope it will be permitted and approved.

Have a great day!

Regares,

SH2023