topic Re: How to use Qlikview table in where filter in SQL query in QlikView

How to use Qlikview table in where filter in SQL query

vijaysahu2 — Wed, 25 Nov 2020 16:16:04 GMT

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)

Re: How to use Qlikview table in where filter in SQL query

zhadrakas — Thu, 02 May 2019 13:24:57 GMT

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)'

Re: How to use Qlikview table in where filter in SQL query

dapostolopoylos — Thu, 02 May 2019 13:31:55 GMT

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))

Re: How to use Qlikview table in where filter in SQL query

vijaysahu2 — Thu, 02 May 2019 15:26:08 GMT

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

Re: How to use Qlikview table in where filter in SQL query

vijaysahu2 — Thu, 02 May 2019 15:26:13 GMT

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

Re: How to use Qlikview table in where filter in SQL query

vijaysahu2 — Thu, 02 May 2019 17:34:14 GMT

It worked and corrected syntax error issue

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

Re: How to use Qlikview table in where filter in SQL query

vijaysahu2 — Thu, 02 May 2019 17:35:45 GMT

This also worked after correcting incorrect syntax issue

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