Load an outer excluding join

[#1]

Hi,

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

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

[Anonymous]

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.

[#3]

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

[Anonymous · Accepted solution]

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;

[#5]

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?

 

Thanks again.

[Anonymous]

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]

Is it possible to do in set analysis?