topic Equivalent of sql's IN statement for a Resident load in QlikView

Equivalent of sql's IN statement for a Resident load

Fri, 19 Nov 2010 05:54:52 GMT

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?

Equivalent of sql's IN statement for a Resident load

Fri, 19 Nov 2010 07:49:30 GMT

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

Miguel_Angel_Baeyens — Fri, 19 Nov 2010 08:56:49 GMT

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

Equivalent of sql's IN statement for a Resident load

Mon, 22 Nov 2010 00:09:34 GMT

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