Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

Transpose Question

cobo1981
6 - Meteoroid

Hi All,

 

I know this question has been asked and answered  a few times but I am struggling with it anyway. 

 

I am trying to take the below set up:

 

FundFund NameClass NameBasisYear End
Fund 1Fund Name 1Class Name ABasis A8/31/2018
Fund 2Fund Name 2Class Name ABasis A8/31/2018
Fund 3Fund Name 3Class Name ABasis A8/31/2018
Fund 4Fund Name 4Class Name ABasis A8/31/2018
Fund 5Fund Name 5Class Name ABasis A8/31/2018
Fund 6Fund Name 6Class Name ABasis A8/31/2018
Fund 7Fund Name 7Class Name ABasis A8/31/2018
Fund 8Fund Name 8Class Name ABasis A8/31/2018
Fund 9Fund Name 9Class Name ABasis A8/31/2018



and change it to this:

 

FundFund 1Fund 2Fund 3Fund 4Fund 5Fund 6Fund 7Fund 8Fund 9
Fund NameFund Name 1Fund Name 2Fund Name 3Fund Name 4Fund Name 5Fund Name 6Fund Name 7Fund Name 8Fund Name 9
Class NameClass Name AClass Name AClass Name AClass Name AClass Name AClass Name AClass Name AClass Name AClass Name A
BasisBasis ABasis ABasis ABasis ABasis ABasis ABasis ABasis ABasis A
Year End8/31/20188/31/20188/31/20188/31/20188/31/20188/31/20188/31/20188/31/20188/31/2018

 

Any help would be appreciated.

 

Thanks in advance!!

8 REPLIES 8
OldDogNewTricks
10 - Fireball

I think the attached workflow will accomplish it for you.

 

TransposeCrossTabSampleSS.jpg

CharlieS
17 - Castor
17 - Castor

I used a few extra tools to assign record and order numbers to make sure the data was transformed exactly as requested. When string fields are used to transpose, they are often sorted to alphabetic order so assigning numeric order fields are a safe way to go.

 

Check out the attached solution and let me know if you have any questions.

OldDogNewTricks
10 - Fireball

@CharlieS - good call with the recordID additions.

cobo1981
6 - Meteoroid

This is fantastic, thank you both for help.

Liz001
7 - Meteor

Hi,

 

I have a similar question with a slightly different output. I want to transpose the attached data and I need it to look like the Output spreadsheet. 

 

I used the workflow developed by CharlieS but it needs a change which I cannot figure out. Any chance someone could make this work? I could use the Select tool and do the renaming of the columns, but my data constantly changes and I don't want to be changing the names in the Select tool everytime I add more data. 

 

Many thanks,

 

Alba

 

 

OldDogNewTricks
10 - Fireball

The workflow that I provided to the original poster should work for you with 1 tweak, because you are grouping by two fields I created a single field to join the two together to use as a header.  Please see the attached workflow.

Liz001
7 - Meteor

Thank you! It worked!

OldDogNewTricks
10 - Fireball

No problem.  Feel free to mark as the answer.

Labels