?transpose
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Labels:
- ADAPT
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@vivian123
Maybe something like this?