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

Transform dimensions of table.

jcyc
7 - Meteor

Hi, I was wondering if Alteryx had to the capability to transform the dimensions of the data. Here's what my data looks like:

 

Grade AbbrevABCD
 GradePre-K1st Grade2nd Grade3rd Grade
7/20/201720293116
8/20/201721283015
9/20/201723243617
10/20/201725253519
11/20/201728231420

 

here is how I need it to be:

 

Grade AbrevGradeDate# of Students
APre-K7/20/201720
APre-K8/20/201721
APre-K9/20/201723
APre-K10/20/201725
APre-K11/20/201728
B1st Grade7/20/201729
B1st Grade8/20/201728
B1st Grade9/20/201724
B1st Grade10/20/201725
B1st Grade11/20/201723
C2nd Grade7/20/201731
C2nd Grade8/20/201730
C2nd Grade9/20/201736
C2nd Grade10/20/201735
C2nd Grade11/20/201714
D3rd Grade7/20/201716
D3rd Grade8/20/201715
D3rd Grade9/20/201717
D3rd Grade10/20/201719
D3rd Grade11/20/201720

 

Being new to Alteryx, I tried using the append tool but i'm not quite getting the results I intended.  Any help is appreciated.

5 REPLIES 5
LordNeilLord
15 - Aurora

Hey @jcyc

 

  1. First transpose your data grouping on Grade Abbrev
  2. Filter on the rows the have Grade in the Grade Abbrev column
  3. Use a join tool connecting the true to left and false to right..using name as your join criteria

This will give you the right result

tcroberts
12 - Quasar

I think the tool you're looking for is the Transpose tool, but your input table isn't a great format for working with in Alteryx because you have two field indexes (Grade Abbrev and Grade). I've provided a sample workflow where I made the second row there the field names, and created the abbreviations afterwards.

 

transposegrades.PNG

 

Essentially, you want to Transpose, using the date field (The called "Grade" for me) as the Key, and the others are the data. This will get the correct dimensions you're looking for. I then use a Switch expression to create the Grade Abbrev based on Grade, Select Tool to rename/reorder things, and Sort tool to sort by the Grade Abbrev to get exactly what you have below.

 

Let me know if this helps,

 

Cheers!

jcyc
7 - Meteor

That worked!

 

Question for you: is there a way to automate what the Switch Formula is trying to do? I ask because what if the data set has grades going up to for example Grad school. There'd be a lot of manual typing of how to assign what grade to which grade abbrev.

tcroberts
12 - Quasar

Given that the Abbreviations need to be ordered the same way as the classes, I'd recommend either creating a lookup table in excel. You could do this by copying your original top 2 rows (the abbreviation, and the grade name), and then using Paste Special (Transpose). If you need help with that let me know.

 

Then you could read this in, and instead of using the Formula tool you could just Join on Grade.

 

Another option would be to add a Summarize Tool, and Group By Grade. You'll now have a list of unique grades. You can then create a formula something like:

 

automateabbrev.PNG

 

From here you could join back in. The problem with this approach is that it is difficult to sort the Names in the order you'd like.

 

Let me know if either of these solutions works for you,

 

Cheers!

abhinavrpk
6 - Meteoroid

What is the way to reverse the sequence? If the end point is my start point and I want to make a table like the start point in this illustration ?

Labels