Where Exists Mechanics

rebelfox — Wed, 10 Feb 2016 11:54:36 GMT

I have a set of invoice headers in a QVD extracted from the source database. The QVD is a subset of all the possible headers on the source database ( an AS400 ).

I now want to load all the detail lines from the AS400 source system for the invoices in the QVD. I believe I can do it using the WHERE EXISTS option. If I do this I believe all of the details table will be loaded into memory on my PC from the AS400 and then the WHERE EXISTS will be processed. Is this true? If this is the case I am better off doing a join and header selection on the AS400 and returning the results for better performance as the details file is huge.

The WHERE EXISTS option

[HEADERS]:
LOAD DISTINCT BRE19 AS KNT08
FROM
[HEADERS.qvd]
(qvd);

ODBC CONNECT TO QLIKVIEW_AS400_ODBC;
[DETAILS]:
LOAD KNT01,
     KNT02,
     KNT03,
     KNT04,
     KNT05,
     KNT06,
     KNT07,
     KNT08
    WHERE EXISTS(KNT08);
SQL SELECT *
FROM AS400.BIGDETAIL;

The AS400 Option Including the Condition Used To Create The QVD Headers File

ODBC CONNECT TO QLIKVIEW_AS400_ODBC;

[DETAILS]:

LOAD KNT01,

KNT02,

KNT03,

KNT04,

KNT05,

KNT06,

KNT07,

KNT08

SQL SELECT *

FROM AS400.HEADER H

LEFT JOIN BIGDETAIL BD ON H.KNT08 = BD.BRE19

WHERE H.BRE35 = 2015;

Re: Where Exists Mechanics

swuehl — Wed, 10 Feb 2016 12:38:03 GMT

You are right, the WHERE EXISTS() in your example is executed on your PC, while the SQL statement is executed on the server and will send all lines to your PC for further processing.

So limiting the lines using SQL would probably be better here.

topic Where Exists Mechanics in QlikView

Where Exists Mechanics

Re: Where Exists Mechanics