Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load an outer excluding join

Hi,

How can I use an outer excluding join in my qlikview script, like the illustration below:

OUTER_EXCLUDING_JOIN.png

As far as I'm aware, my options are using left, right, inner and outer join. I guess what I'm looking for is an outer join, minus the inner join.

Thanks

1 Solution

Accepted Solutions
mov
Employee
Employee

Exists works with a field, not * - so you need a key.  For example:

A:

LOAD

A, B, C,

A&B&C as Key

FROM blah;

INNER JOIN (A) LOAD

A, B, C,D,E

A&B&C as Key

FROM blargh;

B:

LOAD

A, B, C

FROM blah

WHERE not exists(Key, A&B&C);

JOIN (B) LOAD

A, B, C,D,E

FROM blargh

WHERE not exists(Key, A&B&C);

DROP TABLE A;

View solution in original post

6 Replies
mov
Employee
Employee

You can create table "inner join of A & B".  Next step is to load "full join A&B" where records don't exist in the "inner join" table.

Not applicable
Author

Thanks for your reply - would that be done using "exists"? if so, is there a way of specifying the table to see whether the records exist or not?

TableA:

Load * from [blah];

Inner join

Load * from [blargh];

TableB:

Load * from [blah];

Outer join

Load * from [blargh]

Where not exists (*);

Or would the "(*)" that I've used need be a key column from blah and blargh?

Thanks

mov
Employee
Employee

Exists works with a field, not * - so you need a key.  For example:

A:

LOAD

A, B, C,

A&B&C as Key

FROM blah;

INNER JOIN (A) LOAD

A, B, C,D,E

A&B&C as Key

FROM blargh;

B:

LOAD

A, B, C

FROM blah

WHERE not exists(Key, A&B&C);

JOIN (B) LOAD

A, B, C,D,E

FROM blargh

WHERE not exists(Key, A&B&C);

DROP TABLE A;

Not applicable
Author

Super, thanks. And I guess, for completeness, if I wanted to do a left/right exclude like the images below, I'd remove one or the other of the where clauses?

LEFT_EXCLUDING_JOIN.png  RIGHT_EXCLUDING_JOIN.png

Thanks again.

mov
Employee
Employee

If I understand you correctly - it is simpler than that.  For the first picture, load B, and after that load A excluding records existing in B:

B:

LOAD

A, B, C, D, E

A&B&C as Key

FROM blargh;

A:

LOAD

A, B, C

FROM blah

WHERE not exists(Key, A&B&C);

DROP TABLE B;

For the second - just inverse...

danimelo1
Creator
Creator

Is it possible to do in set analysis?