community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Pivot Dynamic Data within Groups

Meteoroid

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

Bolide

@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

Highlighted
Quasar

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

 

 

Quasar

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

Meteoroid

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.

Quasar

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

 

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

How do I Cross Tab without having my data rearranged?

 

Let us know how this works!

Labels