Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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

View solution in original post

3 Replies
Not applicable
Author

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

Miguel_Angel_Baeyens

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
Author

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