Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

create rows based on column values

mupad2008
6 - Meteoroid

Hi All,

 

Newbie to Alteryx here, just got setup with it. Am trying to prep my data set for further analysis.
I have 4 fields that specify each record, the other 3 columns gives units assembled in a given month.
I want tabulate it by adding 2 new columns, units and month, while repeating first 4 fields to specify records. 
Unable to crack it on my own, looking forward to some support from u guys.

 

Cheers!  

 

BEFORE      
ACCSOURCEMARKETGRADEAug-17Sep-17Oct-17
ALPHCOMAPACM1342742
ALPHCOMMEM8172113
GAMANSIAPACF5899784
GAMANSIMEG4565148
       
AFTER      
ACCSOURCEMARKETGRADEUNITSMONTH 
ALPHCOMAPACM134Aug-17 
ALPHCOMMEM817Aug-17 
GAMANSIAPACF589Aug-17 
GAMANSIMEG456Aug-17 
ALPHCOMAPACM127Sep-17 
ALPHCOMMEM821Sep-17 
GAMANSIAPACF597Sep-17 
GAMANSIMEG451Sep-17 
ALPHCOMAPACM142Oct-17 
ALPHCOMMEM813Oct-17 
GAMANSIAPACF584Oct-17 
GAMANSIMEG448Oct-17 

 

6 REPLIES 6
BenMoss
ACE Emeritus
ACE Emeritus
Hi!

Check out the transpose tool.

The key fields are those that you want on each row and then the data fields will be your months.

Ben
NicoleJohnson
ACE Emeritus
ACE Emeritus

Use the Transpose Tool!

You'll choose the fields you want to include on each row as the Key Fields (ACC, SOURCE, MARKET, and GRADE in your example below), and then leave the remaining fields (the months) as your Data Fields. This tool will transpose the month fields to rows, with the header of Name for the month and Value for the units.

 

Hope that helps! 

 

Cheers,

NJ

BenMoss
ACE Emeritus
ACE Emeritus

Here is a solution with your data too!

 

You may note the 'Dynamic or Uknown fields' option is selected as a data field, what that means is any new fields that come into your datasource will be pivoted too. So if you have new months coming in they will be treated in the same way.

 

Ben

 

mupad2008
6 - Meteoroid

Hi Ben, thanks a lot for the quick solution, yes I truncated the data for example, there's a whole lot of forward month projections on the data set, which are working just fine with the solution u provided.

Thanks!

mupad2008
6 - Meteoroid

Hi NJ, thanks for the quick revert, all set now with your approach.

Best!

Dave_C
5 - Atom

What do you mean "quick revert" ?

Labels