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
Solved! Go to Solution.
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
@rrinker2030 I'm pretty new to Alteryx too. Here's how I'd tackle that one. Others may chime in with a better solution:
Looks like @MSalvage and I came up with similar solutions. He beat me to it!
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.
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!