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 | ||||||
ACC | SOURCE | MARKET | GRADE | Aug-17 | Sep-17 | Oct-17 |
ALPH | COM | APAC | M1 | 34 | 27 | 42 |
ALPH | COM | ME | M8 | 17 | 21 | 13 |
GAM | ANSI | APAC | F5 | 89 | 97 | 84 |
GAM | ANSI | ME | G4 | 56 | 51 | 48 |
AFTER | ||||||
ACC | SOURCE | MARKET | GRADE | UNITS | MONTH | |
ALPH | COM | APAC | M1 | 34 | Aug-17 | |
ALPH | COM | ME | M8 | 17 | Aug-17 | |
GAM | ANSI | APAC | F5 | 89 | Aug-17 | |
GAM | ANSI | ME | G4 | 56 | Aug-17 | |
ALPH | COM | APAC | M1 | 27 | Sep-17 | |
ALPH | COM | ME | M8 | 21 | Sep-17 | |
GAM | ANSI | APAC | F5 | 97 | Sep-17 | |
GAM | ANSI | ME | G4 | 51 | Sep-17 | |
ALPH | COM | APAC | M1 | 42 | Oct-17 | |
ALPH | COM | ME | M8 | 13 | Oct-17 | |
GAM | ANSI | APAC | F5 | 84 | Oct-17 | |
GAM | ANSI | ME | G4 | 48 | Oct-17 |
Solved! Go to Solution.
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
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
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!
Hi NJ, thanks for the quick revert, all set now with your approach.
Best!
What do you mean "quick revert" ?