Early bird tickets for Inspire 2023 are now available! Discounted pricing closes on January 31st. Save your spot!

Alteryx Designer Discussions

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

Transpose sets of multiple columns into rows?

pythorlh
6 - Meteoroid

I have data that is poorly normalized.

 

Something like this:

 

student IDClassScoreGradeClass2Score2Grade2Class3Score3Grade3
1History95AEnglish65D-Science85B
2Science90A-Math80C-   

 

And in the end I want this:

 

Student IDClassScoreGrade
1History95A
1English65D-
1Science85B
2Science90A-
2Math80C-

 

I'm trying Transpose, then trimming the numbers off the name column, but I don't know how to bring the 3 records back together correctly.  I'd rather not do it manually, because there are actually 121 of each field (Class, Score, Grade).

3 REPLIES 3
JosephSerpis
16 - Nebula
16 - Nebula

Hi @pythorlh I have mocked up a workflow that produces you output. It uses a regex tool to replace the numbers in your column names. Then I use a Multirow tool to create a rank per student ID for each class they attend, which when you use a crosstab tool produces a row per class for each student ID.

 

Transpose_140819.PNG

Garrett
11 - Bolide

I think you can probably get this done with a single "Arrange" tool (perhaps followed by a "Filter" tool to get rid of any nulls):

 

Arrange Tool.PNG

pythorlh
6 - Meteoroid

Thank you, I was able to get this to work.

 

One note for anyone reading this, make sure if you have any Select tools in your workflow before the Multi-Row Formula Tool that they don't alter the sort of the incoming fields.  I was having trouble until I realized this was happening.

Labels