topic Re: Qlik Sense Oracle Connection load with Exists in App Development

Qlik Sense Oracle Connection load with Exists

rpavan17 — Sat, 16 Nov 2024 20:34:57 GMT

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

Re: Qlik Sense Oracle Connection load with Exists

Vegar — Wed, 26 Jun 2019 05:00:54 GMT

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

Re: Qlik Sense Oracle Connection load with Exists

Vegar — Wed, 26 Jun 2019 05:04:12 GMT

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";

Re: Qlik Sense Oracle Connection load with Exists

rpavan17 — Wed, 26 Jun 2019 05:46:39 GMT

Hi,

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

Thank You

Re: Qlik Sense Oracle Connection load with Exists

rpavan17 — Wed, 26 Jun 2019 05:53:31 GMT

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";

Re: Qlik Sense Oracle Connection load with Exists

Vegar — Wed, 26 Jun 2019 06:32:08 GMT

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

Re: Qlik Sense Oracle Connection load with Exists

Vegar — Wed, 26 Jun 2019 09:00:56 GMT

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