how to create a new column based on multiple columns values in script?

[Anonymous]

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!

[swuehl · Accepted solution]

There are different approaches, but one could be using CROSSTABLE LOAD prefix:

The Crosstable Load

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

[Anonymous]

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?

[swuehl]

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?

[Anonymous]

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

[swuehl]

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;