?transpose

[vivian123]

Hi all, 

I'm wondering what the best way to work through this is? I'm thinking the transpose tool but am not sure how to go about this afterwards. 

I have this table:

Student ID	Week 1 grade	Week 2 grade	Week 3 grade	Week 4 grade	Week 5 grade
111	60		80	90
222		90	80
333			50		70

I'd like the output table to have the grade from the earliest week, which week this was, and the total number of grade- output table:

Student ID	First grade	Week	Total number of grades
111	60	1	3
222	90	2	2
333	50	3	2

Is this possible?

Thanks!

[apathetichell]

Yes! And I'm going to point you in the right direction instead of giving you a workflow...

Here's what you'll need to do to start:

1) transpose (student id as key column).

2) Your name column will now have the various weeks - and your value column will have the grades. Perhaps a summarize tool with some options (include a count non-null) could help?... You'll have to rejoin using student id as a primary key after.

3) after you've rejoined you have the total number of grades and the student id but you need to get your first grade and where that ocured. You can use the original transposed version to get that. You can isolate week from [name] by creating a new field in the formula tool. you can use regex_replace([name],".*(\d+).*","$1") to get the number from the [name] column...

4) now at 3/4 of the way done you'll have to find first grade. There's a few ways to do this. multi-row formula. sample tool, summarize. play around with and come back if you are totally lost.

•  

[Qiu]

@vivian123 
Maybe something like this?