Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Check out our powerful new search update! You can read more here. Please let us know if you have any feedback by creating a topic here.
SOLVED

Transform dimensions of table.

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.

Highlighted
Alteryx Certified Partner

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

Highlighted
Alteryx Partner

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!

Highlighted
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.

Highlighted
Alteryx Partner

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!

Labels