topic Re: outer left join in QlikView

outer left join

souadouert — Wed, 20 Dec 2017 08:50:52 GMT

there is the possibility to use the outer left join in loading script ?

Re: outer left join

Or — Wed, 20 Dec 2017 08:52:39 GMT

Load Field1, Field2

From Table1;

Left Join

Load Field2, FIeld3

From Table2;

Note that if your tables aren't loaded in succession, you can use:

Table1:

Load Field1, Field2

From Table1;

...

(More loads here)

...

Left Join(Table1)

Load Field2, FIeld3

From Table2;

Re: outer left join

souadouert — Wed, 20 Dec 2017 08:55:10 GMT

hELLO,
OUTER LEFT JOIN , not join , i want to extract line exsitin table 1 and not exist in table 2

Re: outer left join

sushil353 — Wed, 20 Dec 2017 08:55:11 GMT

To use the left join

Table1:

Load Key,fields from X;

left join(Table1)

Load Key, Fields2 from Y;

I think left and outer left is same

HTH

Sushil

Re: outer left join

YoussefBelloum — Wed, 20 Dec 2017 09:00:35 GMT

Extracting existing lines on table 1 and not existing in table2 ==> you need left join

Re: outer left join

Or — Wed, 20 Dec 2017 09:00:46 GMT

That's not an outer left join. In SQL, that would be a MINUS operator.

In Qlik, you handle this with NOT EXISTS - see the following for examples:

https://community.qlik.com/docs/DOC-9083

Re: outer left join

maxgro — Wed, 20 Dec 2017 09:05:53 GMT

you can use a left join

Join ‒ QlikView

IMOHO

LEFT JOIN and LEFT OUTER JOIN are the same

Re: outer left join

vvvvvvizard — Wed, 20 Dec 2017 09:09:43 GMT

Qlikview has inner join , left join , right join , join (which is default outer ) only

Re: outer left join

souadouert — Wed, 20 Dec 2017 09:19:28 GMT

Hello youssef ,

left join all lines existing in table 1 with the intersection (table2) if is possible , i need the outer left

Re: outer left join

YoussefBelloum — Wed, 20 Dec 2017 09:39:05 GMT

Souad, I think you should attach 2 tables example here

Re: outer left join

souadouert — Wed, 20 Dec 2017 09:51:32 GMT

Re: outer left join

YoussefBelloum — Wed, 20 Dec 2017 09:58:22 GMT

I think here you need the WHERE NOT EXISTS() with RIGHT JOIN

Table2:

LOAD *;

FROM...

right join

Table1:

LOAD *

FROM...

WHERE NOT EXISTS(id);

Re: outer left join

souadouert — Wed, 20 Dec 2017 10:05:15 GMT

thank you youssef

Re: outer left join

Anonymous — Wed, 20 Dec 2017 10:06:26 GMT

can you try this once...

T1:

Load * Inline

[

id,a

1,aa

2,bb

3,cc

4,dd

];

T2:

Load * Inline

[

id,b

1,zzz

2,yyy

5,qqq

6,ccc];

NoConcatenate

T3:

Load *,id as id1 Resident T2;

right join

Load * Resident T1

where not Exists(id1,id);

drop Field id1;

drop table T1,T2;

Re: outer left join

vvvvvvizard — Wed, 20 Dec 2017 18:15:54 GMT

What you describing is called a left join in QlikView, no need for the

outer keyword

Re: outer left join

MarcoWedel — Wed, 20 Dec 2017 21:20:18 GMT

What if one id not existing in table2 does exist more than one time in table1?