Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Equivalent of sql's IN statement for a Resident load

I have a sql table which has 50 columns labelled DX1, DX2.. etc as well as other columns.

I'm loading this table in, and now want to create a new table, which is a filtered version of the SQL Load.

The filter is that one of the 50 DX columns must be one of 6 possible values ('J45','J450','J451','J458','J459','J46')

In SQL i would do this using the IN statement as follows

WHERE (DX1 IN ('J45','J450','J451','J458','J459','J46')

OR DX2 IN ('J45','J450','J451','J458','J459','J46')

OR DX3 IN ('J45','J450','J451','J458','J459','J46')

OR DX4 IN ('J45','J450','J451','J458','J459','J46')

etc



This doesn't work for a Resident load though. I've also tried using MATCH, but this didn't seem to work.

Any ideas?

1 Solution

Accepted Solutions
Not applicable

Equivalent of sql's IN statement for a Resident load

I've found the problem.. I think the match was having issues when one of the DX values was null.

I AND'ed each MATCH with an ISNULL and it worked fine.

Cheers for your help guys

3 Replies
Not applicable

Equivalent of sql's IN statement for a Resident load

I think U may create new table (Table1 e.g.) , fill her with your values manually, and then load * from ..... where exists(Table1)

Equivalent of sql's IN statement for a Resident load

Hello Matt,

The following code will work in any LOAD statement:

Table:LOAD *WHERE MATCH(DX1,'J45','J450','J451','J458','J459','J46')OR MATCH(DX2,'J45','J450','J451','J458','J459','J46')OR MATCH(DX3,'J45','J450','J451','J458','J459','J46')OR MATCH(DX4,'J45','J450','J451','J458','J459','J46');SQL SELECT *FROM Database.Table;


When there are a lot of values you want to compare with and they are stored in any datasource, a mapping table/applymap will work in the where clause as well:

LoadTableMap: // This map is only used while the script is being excecutedMAPPING LOAD DX1 1 AS LOADWHERE LEFT(DX1, 3) = 'J45';SQL SELECT DX1FROM Database.Table; Table:LOAD *WHERE ApplyMap('LoadTableMap', DX1, '$') <> '$'; // It will load all existing values in previously loaded DX1SQL SELECT *FROM Database.Table;


Hope this helps

Not applicable

Equivalent of sql's IN statement for a Resident load

I've found the problem.. I think the match was having issues when one of the DX values was null.

I AND'ed each MATCH with an ISNULL and it worked fine.

Cheers for your help guys

Community Browser