Transform dimensions of table.

[jcyc]

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 Abbrev	A	B	C	D
Grade	Pre-K	1st Grade	2nd Grade	3rd Grade
7/20/2017	20	29	31	16
8/20/2017	21	28	30	15
9/20/2017	23	24	36	17
10/20/2017	25	25	35	19
11/20/2017	28	23	14	20

here is how I need it to be:

Grade Abrev	Grade	Date	# of Students
A	Pre-K	7/20/2017	20
A	Pre-K	8/20/2017	21
A	Pre-K	9/20/2017	23
A	Pre-K	10/20/2017	25
A	Pre-K	11/20/2017	28
B	1st Grade	7/20/2017	29
B	1st Grade	8/20/2017	28
B	1st Grade	9/20/2017	24
B	1st Grade	10/20/2017	25
B	1st Grade	11/20/2017	23
C	2nd Grade	7/20/2017	31
C	2nd Grade	8/20/2017	30
C	2nd Grade	9/20/2017	36
C	2nd Grade	10/20/2017	35
C	2nd Grade	11/20/2017	14
D	3rd Grade	7/20/2017	16
D	3rd Grade	8/20/2017	15
D	3rd Grade	9/20/2017	17
D	3rd Grade	10/20/2017	19
D	3rd Grade	11/20/2017	20

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

[LordNeilLord]

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 · Accepted solution]

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.

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]

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]

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:

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]

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 ?