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 Grade7/20/2017202931168/20/2017212830159/20/20172324361710/20/20172525351911/20/201728231420 here is how I need it to be: Grade AbrevGradeDate# of StudentsAPre-K7/20/201720APre-K8/20/201721APre-K9/20/201723APre-K10/20/201725APre-K11/20/201728B1st Grade7/20/201729B1st Grade8/20/201728B1st Grade9/20/201724B1st Grade10/20/201725B1st Grade11/20/201723C2nd Grade7/20/201731C2nd Grade8/20/201730C2nd Grade9/20/201736C2nd Grade10/20/201735C2nd Grade11/20/201714D3rd Grade7/20/201716D3rd Grade8/20/201715D3rd Grade9/20/201717D3rd Grade10/20/201719D3rd 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.

Transform dimensions of table.

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.

Preparation

Accepted answers

tcroberts

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!

transposegrades.yxmd

All comments

LordNeilLord

Hey @jcyc

First transpose your data grouping on Grade Abbrev
Filter on the rows the have Grade in the Grade Abbrev column
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

Let me know if this helps,

Cheers!

transposegrades.yxmd

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.

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 ?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703