topic Re: Load an outer excluding join in QlikView

Load an outer excluding join

Wed, 18 Feb 2015 16:23:33 GMT

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

Re: Load an outer excluding join

Anonymous — Wed, 18 Feb 2015 16:27:57 GMT

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.

Re: Load an outer excluding join

Wed, 18 Feb 2015 16:49:23 GMT

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

Re: Load an outer excluding join

Anonymous — Wed, 18 Feb 2015 16:59:34 GMT

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

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;

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;

Re: Load an outer excluding join

Wed, 18 Feb 2015 17:08:35 GMT

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.

Re: Load an outer excluding join

Anonymous — Wed, 18 Feb 2015 17:25:55 GMT

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:

LOAD

A, B, C, D, E

A&B&C as Key

FROM blargh;

LOAD

A, B, C

FROM blah

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

DROP TABLE B;

For the second - just inverse...

Re: Load an outer excluding join

danimelo1 — Fri, 22 Dec 2017 14:12:04 GMT

Is it possible to do in set analysis?