Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Load Resident with Join exclusion

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?

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Load Resident with Join exclusion

Try this.


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


4 Replies
mov
Esteemed Contributor III

Load Resident with Join exclusion

Try this.


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


Not applicable

Load Resident with Join exclusion

Thank you Michael! It worked like a charm Big Smile

MVP
MVP

Load Resident with Join exclusion

Micheal,

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

Ralf

MVP
MVP

Load Resident with Join exclusion

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


Community Browser