SQL Functions (IN) or (BETWEEN) to Qlik Sense Expression

[DanielP065]

Hello,

I've added a column to my data set that I want to calculate the Education Level of students based on their grade_level. I'm having difficulty finding the right functions to help me achieve the task. Are there any functions equivalent to the IN or BETWEEN functions of SQL that can be used to calculate the contents of the new column?  Below is the idea of what I want it to do.  Resulting in their grade_level grouping them into an Education level of Elementary , Middle or High schools.

SELECT
ps.students.lastfirst,
CASE WHEN ps.students.grade_level IN (0, 1, 2, 3, 4, 5) THEN 'Elementary'
WHEN ps.students.grade_level BETWEEN 9 and 12 THEN 'High' END as Level
FROM
ps.students

Thank you for your help
Daniel

[jaimeaguilar]

Hi,

If you're having trouble converting sql queries to qlik language, you can actually use your SQL query directly in Qlik Sense. Especially if you already have several queries containing the reports you wish to include in your qlik sense visualizations. 

You can store the sql query in a qvd file so you can reuse it later and make any modification you want:

tablename:

SQL SELECT //the SQL keyword is optional in Qlik Sense
ps.students.lastfirst,
CASE WHEN ps.students.grade_level IN (0, 1, 2, 3, 4, 5) THEN 'Elementary'
WHEN ps.students.grade_level BETWEEN 9 and 12 THEN 'High' END as Level
FROM
ps.students;

store tablename into lib://folder_connection/filename.qvd(qvd);

Then you can load the qvd file and add or modify any field you want.

In case you want to translate the SQL logic to Qlik logic, then you can use match/wildmatch functions like this:

qlik_table:

//THIS UPPER PART IS QLIK LANGUAGE

LOAD *, if(match(grade_level,0,1,2,3,4,5), 'ELEMENTARY', if(match(grade_level,9,10,11,12), 'HIGH')) as Level;

SQL SELECT //the SQL keyword is optional in Qlik Sense
ps.students.lastfirst, ps.students.grade_level

FROM
ps.students;

store qlik_table into lib://folder_connection/qlik_table.qvd(qvd);

You can execute this block of code and it will do the following:

1. Return to qlik the sql query

2. Add new column(s) with qlik functions (the LOAD part)

3. store the final table into QVD to reuse the file in other apps

If your new to Qlik Sense you may read about some concepts like preceding loads (for combine SQL queries with Qlik functions), data connections (every interaction with external data is considered a connection in Qlik Sense: folders, odbc, rest api, xls files, etc), QVD files for storing tables in local files,

I hope this helps,

regards

[DanielP065]

Thank you!  This is great information.  I'll look into both methods you mention to see which I'll use.  I appreciate your help.