Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table of student grades with 3 columns according to levels: beginner, intermediate and advanced:
student id | beginner | intermediate | advanced |
1 | 90 | ||
2 | 80 | ||
3 | 90 | ||
4 | 40 | ||
5 | 100 |
I would like to create a new column named "level" that will indicate student's level and another column that will gather up all the grades as follows:
student id | level | grade |
1 | beginner | 90 |
2 | intermediate | 80 |
3 | advanced | 90 |
4 | intermediate | 40 |
5 | beginner | 100 |
How do i do that directly in the script?
Thanks in advance!
There are different approaches, but one could be using CROSSTABLE LOAD prefix:
TMP:
CROSSTABLE (level,grade)
LOAD * INLINE [
student id, beginner, intermediate, advanced
1, 90,,
2, , 80,
3, ,, 90
4, , 40,
5, 100,,
];
NoConcatenate
LOAD * RESIDENT TMP
WHERE LEN(grade);
DROP TABLE TMP;
student id | level | grade |
---|---|---|
1 | beginner | 90 |
2 | intermediate | 80 |
3 | advanced | 90 |
4 | intermediate | 40 |
5 | beginner | 100 |
There are different approaches, but one could be using CROSSTABLE LOAD prefix:
TMP:
CROSSTABLE (level,grade)
LOAD * INLINE [
student id, beginner, intermediate, advanced
1, 90,,
2, , 80,
3, ,, 90
4, , 40,
5, 100,,
];
NoConcatenate
LOAD * RESIDENT TMP
WHERE LEN(grade);
DROP TABLE TMP;
student id | level | grade |
---|---|---|
1 | beginner | 90 |
2 | intermediate | 80 |
3 | advanced | 90 |
4 | intermediate | 40 |
5 | beginner | 100 |
Thanks Stefan!
I implemented the below script since i already had a table called "grades" with 4 columns of dimensional data
CROSSTABLE (level,grade,4)
Load* Resident [grades];
DROP Table grades;
It worked great apart from the fact that it removes students that don't have any grades. I would still like to keep them in the table, so i removed the WHERE LEN(grade) from your suggested script but they are still missing.
What can i do to include them?
Not sure why they are still missing when you remove the WHERE clause, it should now show all levels for all students, even when there is no grade.
How would you like to show the student with no grades, given your requested second table layout?
assuming that student 6 does not have any grades the result table should look like this but i am open to suggestions if it's not possible
student id | level | grade |
1 | beginner | 90 |
2 | intermediate | 80 |
3 | advanced | 90 |
4 | intermediate | 40 |
5 | beginner | 100 |
6 |
Maybe like
TMP:
CROSSTABLE (level,grade)
LOAD * INLINE [
student id, beginner, intermediate, advanced
1, 90,,
2, , 80,
3, ,, 90
4, , 40,
5, 100,,
6,,,
];
RESULT:
NoConcatenate
LOAD *, [student id] as student_check RESIDENT TMP
WHERE LEN(grade)
;
Concatenate (RESULT)
LOAD [student id], [student id] as student_check
RESIDENT TMP
WHERE LEN(grade)= 0 and not exists(student_check, [student id])
;
DROP TABLE TMP;
DROP FIELD student_check;