Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
vijaysahu2
Creator
Creator

How to use Qlikview table in where filter in SQL query

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)

 

 

2 Solutions

Accepted Solutions
zhadrakas
Specialist II
Specialist II

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

View solution in original post

dapostolopoylos
Creator III
Creator III

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

Father/Husband/BI Developer

View solution in original post

6 Replies
zhadrakas
Specialist II
Specialist II

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

View solution in original post

dapostolopoylos
Creator III
Creator III

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

Father/Husband/BI Developer

View solution in original post

vijaysahu2
Creator
Creator
Author

Thanks I tried it but it is giving me incorrect syntax error
vijaysahu2
Creator
Creator
Author

Thanks I tried it but it is giving me incorrect syntax error
vijaysahu2
Creator
Creator
Author

It worked and corrected syntax error issue

 

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

vijaysahu2
Creator
Creator
Author

This also worked after correcting incorrect syntax issue

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