Load Resident with Join exclusion

[#1]

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?

[Anonymous · Accepted solution]

Try this.

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

[#3]

Thank you Michael! It worked like a charm

[rbecher]

Micheal,

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

Ralf

[johnw]

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

[Black_Hole]

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.