pivot multiple levels

[jcardoso]

Hi,

although it seems trivial, i went through some similar topics although still couldn't figure how to pivot in multiple levels using crosstab, or other tools!

The idea is to transform the data as the table below. One level is doable, two didn't sort it out, but what if i need more?

Attached, a sample of my data file!

There are some threads about the subject already like below, but didn't sort it, so help is much appreciated!

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Excel-Pivot-Table-with-multiple-rows-a...
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Pivot-table-with-multiple-sub-groups-i...

Thank you so much!

[apathetichell · Accepted solution]

There seems to be more blanks in this sample - but I believe this should be what you want. What I did was make a combined column header of type and year and then crosstabbed that (with week as a key column) as new headers with index values staying as values. Alteryx doesn't really do multi-row headers (until you get to tables or you are ready to export) so it's easier to keep these as combined named headers.

[jcardoso]

Thank you! 
Yes, the sample tool took some records out, so there will be blanks.
That solution would be the same as one level, although, need it in two levels because there are scripts and macros reading from the result file.



 

[apathetichell]

I'm not following - do you need a the row header on the firs two in lieu of a combined header? I believe that otherwise this is the data you wanted? Are your scripts/macros row dependent? If so you'll need to use the output to a specific range option.

[jcardoso]

thanks, i have to accept it as a solution, although its not quite what i need! that works, in the assumption that you merge all the levels you need into one row! 

Thank you!

[apathetichell]

Hi - yes that's true but it allows you to keep field types without sacrificing the information you wanted in the header. Basically it's a choice. You can have two headers but everything will be a string type...