SQL query with where in clause

[wrg356]

I'm facing a problem doing a SQL query through QlikView. I have a table with some IDs that I want to fetch some information via SQL query. It would be like:

Informations:
SQL SELECT
id,
  ...
FROM table
where id in $('ID_QLIK');

The problem is that 'ID_QLIK' is an entire column in a QlikView table. I had success using a variable for a single value, but I need to make the query finding multiple id's in that table.

Is it possible to do it in QlikView?

[QFabian]

Hi @wrg356 , you should use inter-record function EXISTS in your WHERE Clause. It is very usefull for what you want, 

Exists - script function

Exists() determines whether a specific field value has already been loaded into the field in the data load script. The function returns TRUE or FALSE, so can be used in the where clause of a LOAD statement or an IF statement.

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecordFun...

Inter-record functions

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/InterReco...

[wrg356]

Hi @QFabian, thanks for the reply! Can you give me an example how it would be written in a script?

Can I use the Exists function inside a SQL query?

[QFabian]

Hi @wrg356 , exists is a Qlik function, not SQL function.

Here the example :

A:
Load * INLINE [
Id_A
1
3
];

B:
Load * INLINE [
B, Id_B
A, 1
B, 2
C, 3
D, 4
E, 5
];

C:
Load
B as C
Resident B
Where exists (Id_A, Id_B);

Result :

[maxgro · Accepted solution]

you can concat the values of the ID_QLIK field

// some test data
T:
load
ceil(rand()*100) as ID_QLIK
AutoGenerate 5;

// concat the ID_QLIK values in FILTER field 

T2:
LOAD CONCAT(distinct ID_QLIK, ', ') as FILTER RESIDENT T;

// define a variable you can use in the SQL query

LET vSQLFilter = PEEK('FILTER', 0, 'T2');

The variable vSQLFilter is something like

46, 63, 93, 98