How to use Qlikview table in where filter in SQL query

[vijaysahu2]

Hi All

I have created table1 which is populating  data from Oracle source.I want to use it into in where condition in the SQL query to check the distinct DisplayName.

How can I do this ? Please suggest ,below approach is giving me error message

Table1:
LOAD *;
SQL
SELECT DISTINCT QV_EXEC_NAME AS "DisplayName" FROM  Myview; 

QV_UserAccess:
LOAD *;
//get all live users for usage stats
SQL
SELECT
DisplayName
FROM Tab2
WHERE QVAppID = 3
AND ActiveLogin = 1,
AND DisplayName NOT IN  ( Select DisplayName  from Table1)

[zhadrakas · Accepted solution]

if both sources are on the same database i would go like this;

QV_UserAccess:
SQL
SELECT
DisplayName
FROM Tab2
WHERE QVAppID = 3
AND ActiveLogin = 1,
AND DisplayName NOT IN ( SELECT DISTINCT QV_EXEC_NAME AS "DisplayName" FROM Myview)

if not i would do it like this:
Table1:
LOAD '(' & concat(DisplayName, ',') & ')' as DisplayName;
SQL
SELECT DISTINCT QV_EXEC_NAME AS "DisplayName" FROM Myview;

LET vWhereStatement = peek('DisplayName', 0, 'Table1');

drop table Table1;


QV_UserAccess:
//get all live users for usage stats
SQL
SELECT
DisplayName
FROM Tab2
WHERE QVAppID = 3
AND ActiveLogin = 1,
AND DisplayName NOT IN '$(vWhereStatement)'

[dapostolopoylos · Accepted solution]

What i did in a similar case was something like that:

Accounts:
LOAD Distinct
NewAccount as ArrangementIdInSourceSystem
FROM
[D:\Accounts.xlsx]
(ooxml, embedded labels, table is NewAccount);

LET vNoOfRows=NoOfRows('Accounts');

FOR i=1 to $(vNoOfRows)
LET vTmp=FieldValue('ArrangementIdInSourceSystem',$(i));
IF $(i)= 1 THEN
SET vAcc= "'$(vTmp)'";
ELSE
SET vAcc=$(vAcc),'$(vTmp)';
ENDIF
NEXT;

DROP TABLE Accounts;

Data:

SQL

SELECT
AR01T.UNQID,
AR01T.ARID
FROM
        AR01T AS AR01T
WHERE
AR01T.UNQID IN ($(vAcc))

[vijaysahu2]

Thanks I tried it but it is giving me incorrect syntax error

[vijaysahu2]

Thanks I tried it but it is giving me incorrect syntax error

[vijaysahu2]

It worked and corrected syntax error issue

AND DisplayName NOT IN ('$(vWhereStatement)');

[vijaysahu2]

This also worked after correcting incorrect syntax issue

AND DisplayName NOT IN ('($vAcc)');