Pivot Dynamic Data within Groups

[rrinker2030]

Hi All,

I'm new to Alteryx and data blending.  I'm wondering if anyone can help me crack this nut...

I'm trying to pivot data from a long and narrow table into a deep and wide format that my sales team is more comfortable with.  The data comes to me in a format like below:

Category	ProductID	Release	Market	Model	Cost
VH_BA	222	2017	North	2T	11.22
VH_BA	222	2017	North	4T	11.44
VH_BA	222	2017	South	2T	22.22
VH_BA	222	2017	South	4T	22.44
VH_BA	222	2016	North	2T	10.21
VH_BA	222	2016	North	4T	10.41
VH_BA	222	2016	South	2T	20.21
VH_BA	222	2016	South	4T	20.41
VH_BA	333	2017	North	2T	12.23
VH_BA	333	2017	North	4T	12.45
VH_BA	333	2017	South	2T	23.23
VH_BA	333	2017	South	4T	23.45
VH_BA	333	2016	North	3T	14.31
VH_BA	333	2016	North	4T	14.42
VH_BA	333	2016	South	3T	23.31
VH_BA	333	2016	South	4T	23.42

I would like to get it into this format.

		2017	2017	2017	2017	2016	2016	2016	2016	2016	2016
		North	North	South	South	North	North	North	South	South	South
Category	ProductID	2T	4T	2T	4T	2T	3T	4T	2T	3T	4T
VH_BA	222	11.22	11.44	22.22	22.44	10.21		10.41	20.21		20.41
VH_BA	333	12.23	12.45	23.23	23.45		14.31	14.42		23.31	23.42

The number of products in a category will vary, and so won't the models within releases & markets.

I've spent a bunch of time with the transpose and cross tab tools, but I'm not able to get this quite right.  Any help you could offer would be appreciated.

Kind regards,

Rich

[MSalvage · Accepted solution]

@rrinker2030,

I have ran into people wanting similar tables. I don't like having the triple(or more) Header, so I concatenate those fields into a new field 'Header'. Then just do a simple cross tab.

Attached is a Sample of what I am talking about.

Hope it helps,

MSalvage

[ddiesel]

@rrinker2030 I'm pretty new to Alteryx too. Here's how I'd tackle that one. Others may chime in with a better solution:

[ddiesel]

Looks like @MSalvage and I came up with similar solutions. He beat me to it!

[rrinker2030]

Thanks to you both.  The solution seems so much more simple than I tried to make it out to be.

Do you know how I can adjust the order of the fields?  They seem to run ascending from left to right.  I'd like to run them descending, left to right (so the newest model year is farthest left.  Sorting by "header" before the cross tab doesn't seem to do the trick.

[ddiesel]

This stumped me for awhile but I think the attached solution will work for you.

I found this post by @lepome as a good starting point:

How do I Cross Tab without having my data rearranged?

Let us know how this works!