Qlik Sense Oracle Connection load with Exists

[rpavan17]

Hi,

I have Table 1 load from QVD and I would like to load value from Oracle connection where exists from Table 1.

Table1:

Load 

DOC_ID,

DOC_ID AS DOC_ID_DUPLICATE

FROM LIB://.....QVD;

Table 2:

LIB CONNECT TO 'DB';

LOAD DOC_ID,
STAMP_UID,
;

SELECT "DOC_ID",
"STAMP_UID",
FROM "BASW_PROD"."ACTION_LOG"

WHERE EXISTS (DOC_ID_DUPLICATE, DOC_ID);

Could you please help?

Thanks

[Vegar]

When in a SQL Select statement you will need to use SQL syntax and the oracle EXSISTS is not the same as Qlik syntax exists.

I would try to create a variable listing all your duplicate IDs and then use the oracle syntax IN

SQL SELECT

"DOC_ID",

STAMP_UID

FROM

"BASW_PROD"."ACTION_LOG"

Where DOC_ID IN (vListOfIds) ;

[Vegar]

If it's a small data set you could do the exists check in the presiding load like this.

LIB CONNECT TO 'DB';

LOAD DOC_ID,
STAMP_UID

WHERE EXISTS (DOC_ID_DUPLICATE, DOC_ID) ;

SQL  SELECT "DOC_ID",
"STAMP_UID",
FROM "BASW_PROD"."ACTION_LOG";

[rpavan17]

Hi,

Ok. May i know how to create a variable listing for Duplicate IDs(DOC_ID)

Thank You

[rpavan17]

Hi,

Tried this. But did not work.

If it's a small data set you could do the exists check in the presiding load like this.

LIB CONNECT TO 'DB';

LOAD DOC_ID,
STAMP_UID

WHERE EXISTS (DOC_ID_DUPLICATE, DOC_ID) ;

SQL  SELECT "DOC_ID",
"STAMP_UID",
FROM "BASW_PROD"."ACTION_LOG";

[Vegar]

If this doesn't work 

Table1:
Load 
  DOC_ID,
  DOC_ID AS DOC_ID_DUPLICATE
FROM 
  LIB://.....QVD
;

LIB CONNECT TO 'DB';
Table 2:
LOAD 
  DOC_ID,
  STAMP_UID
WHERE 
  EXISTS (DOC_ID_DUPLICATE, DOC_ID);
;

SQL SELECT 
  "DOC_ID",
  "STAMP_UID"
FROM 
  "BASW_PROD"."ACTION_LOG"
;

... then you coudl first load the database values in a TMP table and load from it resident like this.

Table1:
Load 
  DOC_ID,
  DOC_ID AS DOC_ID_DUPLICATE
FROM 
  LIB://.....QVD
;

LIB CONNECT TO 'DB';

TMP:
SQL SELECT 
  "DOC_ID",
  "STAMP_UID"
FROM 
  "BASW_PROD"."ACTION_LOG"
;
Table 2:
NoConcatenate 
LOAD 
  DOC_ID,
  STAMP_UID
FROM TMP
WHERE 
  EXISTS (DOC_ID_DUPLICATE, DOC_ID);
;
DROP TABLE TMP;

If neither of these are working then you should check your database connection and SELECT query.

-Vegar

[Vegar]

If you want the IN approach try something like this:

DUPLICATES:
Load 
  chr(39)&concat(distinct DOC_ID, chr(39)&','&chr(39))&chr(39) as 
  DOC_ID_DUPLICATEs,
FROM 
LIB://.....QVD ;

LET vDuplicatesList = peek('DOC_ID_DUPLICATEs',-1,'DUPLICATES') ;

DROP TABLE DUPLICATES;

LIB CONNECT TO 'DB';
Table 2:
SQL SELECT 
  "DOC_ID",
  "STAMP_UID"
FROM 
  "BASW_PROD"."ACTION_LOG"
WHERE DOC_ID IN ($(vDuplicatesList ))
;

-Vegar