topic Load Resident with Join exclusion in QlikView

Load Resident with Join exclusion

Wed, 17 Jun 2009 02:16:35 GMT

Hi, I'm a new QlikView user & would like to perform a LOAD RESIDENT using another table to exclude values in my load: Table tempPOS has been created, joining 2 different data sources into 1 with a POSID string value as the primary key. From tempPOS, I created a POSException table to write rows that had invalid value combinations POSException also contains a POSID column. Now I would like to create a POSTransaction table using a LOAD [column list] RESIDENT tempPOS where POSID is not in POSException. In other words, I'd like to exclude exception transactions that were written to the POSException table. Can you help?

Load Resident with Join exclusion

Anonymous — Wed, 17 Jun 2009 02:34:42 GMT

Try this.


POSException:
LOAD DISTINCT
 POSID,
 POSID as EXCLUDE
 ...
RESIDENT ...
...
POSTransaction:
LOAD
 POSID,
 ...
RESIDENT tempPOS
WHERE not exists(EXCLUDE, POSID);

Load Resident with Join exclusion

Wed, 17 Jun 2009 04:57:31 GMT

Thank you Michael! It worked like a charm

Load Resident with Join exclusion

rbecher — Wed, 17 Jun 2009 05:13:12 GMT

Micheal,

thank you for that. Sometimes one have to think around a corner.

Ralf

Load Resident with Join exclusion

johnw — Wed, 17 Jun 2009 06:18:44 GMT

For large data sets, I suspect it would be more efficient for both memory and CPU to do something like this:

POSTransaction:
LOAD <fields>
FROM <first data source>
;
LOAD <fields>
FROM <second data source>
;
INNER JOIN
LOAD POSID
RESIDENT POSTransaction
WHERE NOT (<condition you were using for POSexception>)
;

Re: Load Resident with Join exclusion

Black_Hole — Wed, 15 May 2019 09:29:53 GMT

Hello @johnw ,

I would like to know if your solution can be used also in the case where the data source is an ODBC connection.

Because, I try it and I have an error message.

Thank you in advance for your help.