Can Output be Stacked into Multiple Columns?

[chockalingam]

I am looking for suggestions on how to convert the following output into a pivot table format like second screenshot where columns are stacked upon each other. 

I have tried the arrange, cross tab and transpose tools with little success.

[Pilsner · Accepted solution]

Hello @chockalingam 

I have given this problem a go and come up with the following solution:

A couple of things to note.

Firstly, I don't believe you can have multiple rows all as column headers in Alteryx so to get around this I created a Key which is unique to each "Area" + "Status" + "Food Name" combo. This key then forms the column headers. Using multiple Cross Tab tools and a Union tool, this allowed me to create a row each for the "Area", "Status" and "Food Names", and then stack them on top of each other so that the output looks similar to the screenshot you provided. By enforcing the order in the union tool I have made sure the order matches your screenshot. Finally the 4th cross tab is used to pivot out the numeric data values per date, with the unique column headers I created. This is also unioned on to the rest of the data.

Secondly, I noticed that in your screenshot of a sample output the data 1/1/2024 is not present and the value "Date" appears to have overwritten it. In my version I have instead put the value of Date in all the empty spaces at the top of the data column as you can see below. I hope this still works. 

Finally, I didn't copy the input data exactly from your screenshot so please don't expect the image below to match your screenshot in terms of the numeric values or dates. You should be able to simply hook your data up to the workflow I have provided below to get the required results.

Please let me know how you get on with this. The tools in the workflow have been annotated but I'm happy to explain the workflow further if needed.

Regards - Pilsner

[KGT · Accepted solution]

Great response from @Pilsner to match the output.

However, I'll say that having the heading in 3 rows is not advisable. That is no longer a data table, but rather a report. Most of the solution is purely to accommodate that 3 row heading which is not best practice for any data process.

If you don't need the 3 rows of headings then you can use a formula to concatenate the 3 columns after the Transpose ([Area] + " - " + [Status] + " - " + [Name]) and then CrossTab that with Date as the Group field, and your created field as the header.

[chockalingam]

This is extremely helpful - thanks for offering this solution.  I want to retain my column order as my initial screenshot:

Onions | Ginger | Nutella | Bananas

Since they are being alphabetized as part of the Cross-Tab tool, what modifications could be made?

[KGT]

Simple way is to rename before the crosstab to put 1,2,3,4 in front of them with a Replace(Replace([Name],"Onions","1-Onions"),"Ginger","2-Ginger")... and so on. Keeping the numbers on there means it sortable in the future as well. 

Hint: In a select tool, you can click the name header to sort by Alphabetical Order.

If you wanted to play with the name a bit, I would create a lookup table with the different names and then you can use a dynamic rename to replace according to that table. So, for instance, your table could be like

Name, SortName

Onions, 1-Onions

Ginger, 2-Ginger

And then use that to rename the fields afterwards if you don't want to keep the numbers on there. 

[lwolfie]

Were you able to find a solution?  I've been able to produce something similar using the reporting tools table tool.  You have to separate the header lines from the data.  Create separate tables for each line and combine then and then you combine them back with your data.  You data table would output without the header in it.  I found the solution on community, but I can't find it again.  This is the closest I can currently find to what I did.  If you 

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Pivot-table-with-multiple-sub-...

If you still need assistance, I can try to draw up a workflow if you provide sample data.