Transpose table

[AlbertoRefra]

Hi, I need to transpose a table taking into account different variables. 

The fields to be considered to group data are 'Plant' and 'Storage Location'. 

The transposal is to be done for 'Quantity' and 'Amount' fields.

The reference period (field 'Ref_period') should be used as column header, adding the prefix "Quantity" and "Amount" based on the related transposed value. 

In the attached file you can find an example of the "Starting Table" and the expected "Output". 

The objective is to have a view month by month of the quantity and amount of the stock of the storage locations of a plant. 

The idea is to see Quantity and Amount of month 1, Quantity and Amount of month 2 and so on...

The reference period could differ everytime I run the analysis, so I would like to sort the output columns whichever the reference periods could be.

I'd like to have some advices, since I can't find a way to automate all the logics. Thank you!

[lwolfie]

Have you tried the Arrange tool instead?

[AlbertoRefra]

I tried but I wasn't able to order the columns in the expected order (Quantity month 1, Amount month 1, Quantity month 2, Amount month 2...) 

Attached the workflow, as you can see the columns are not ordered as they should

[lwolfie]

You can try something like the attached.  I had to take out my input files, but you should be able to get the gist of what I did.  Basically you transpose the data, merge the fields and the do a cross tab again.  You also process the headers separately.  I used something from community to help me develop this, but for the life of me I can't find it at the moment.

[AlbertoRefra]

Not sure I got it, sorry. I took a look but it's not easy for me to understand without data... 
Any chances you/someone can give an example with the workflow that I shared in the previous comment?

[lwolfie]

Your input files haven't been coming through.  That's why I didn't build off yours.

[AlbertoRefra · Accepted solution]

The input file is the one attached to my post ("Sample Table", tab 'Starting Table'). 

Anyway I think I found a solution: starting from the "Transpose Table.ymxd" workflow above, after the Cross Tab tool I split "Quantity" and "Amount" columns using Dynamic Select tool (to keep Plant, Storage Location and all fields containing "Quantity" or "Value" in their title). 

In both streams, I transpose the 2 tables (with "Plant" and "Storage Location" as Key Columns) and add a record ID. 

Using the Union tool I merge the 2 streams and I sort by Record ID: in this way I can have in a unique column "Quantity" and "Value" ordered as needed. 

In the end, I can reach the final output format with a last Cross Tab tool grouping again by "Plant" and "Storage Location".