Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
agdgn123
New Contributor III

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

Hi,

I have a table of student grades with 3 columns according to levels: beginner, intermediate and advanced:

    

student idbeginner intermediateadvanced
190
2 80
3 90
4 40
5100

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 idlevelgrade
1beginner90
2intermediate80
3advanced90
4intermediate40
5beginner100

How do i do that directly in the script?

Thanks in advance!

1 Solution

Accepted Solutions
MVP
MVP

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

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
1beginner90
2intermediate80
3advanced90
4intermediate40
5beginner100
5 Replies
MVP
MVP

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

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
1beginner90
2intermediate80
3advanced90
4intermediate40
5beginner100
agdgn123
New Contributor III

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

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?

MVP
MVP

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

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?

agdgn123
New Contributor III

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

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 idlevelgrade
1beginner90
2intermediate80
3advanced90
4intermediate40
5beginner100
6
MVP
MVP

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

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;