Solved: Re: Doubt in Left join - Qlik Community

Anonymous · ‎2017-02-24

What will be the result after applying Left join in the three tables?

sunny_talwar · ‎2017-02-24

If I leave the second join and run this script

A:

LOAD * INLINE [

F1, F2, F3

A, aa, aaa

B, bb, bbb

C, cc, ccc

];

Left Join (A)

LOAD * INLINE [

F1, F2, F4

A, aa, 10

B, , 15

C, , 20

];

My output is this

Now imagine that you wanted to do a left join to this table

Left Join (A)

LOAD * INLINE [

F1, F3, F4

A, , 10

B, bbb, 15

C, ccc, 20

];

What is the result do you expect to see? Would you think that you will only join on F1 and F3 or F1, F3, & F4? There is a certain order of execution here and you cannot just expect QlikView to know what you are looking for. There are work arounds but the existing script won't give what you are looking for.

This can be your work around

A:

LOAD * INLINE [

F1, F2, F3

A, aa, aaa

B, bb, bbb

C, cc, ccc

];

Left Join (A)

LOAD * INLINE [

F1, F2, F4

A, aa, 10

B, , 15

C, , 20

];

Left Join (A)

LOAD * INLINE [

F1, F3, F5

A, , 10

B, bbb, 15

C, ccc, 20

];

FinalA:

LOAD F1,

F2,

F3,

Alt(F4, F5) as F4

Resident A;

DROP Table A;

View solution in original post

sunny_talwar · ‎2017-02-24

May be like this

F1 F2 F3 F4

A aa aaa 10

B bb bbb -

C cc ccc -

The table C will have no impact on the join

Anonymous · ‎2017-02-24

Hi Sunny

Why the table C will have no impact?

sunny_talwar · ‎2017-02-24

Because after the first left join, F4 is already a column in Table A. So now the second left join will only join where F1, F3 and F4 Match (unlike the first left join which was taking place for F1 and F2)

Anonymous · ‎2017-02-24

I guess second Left join ( of Table C ) will be based on F1 and F3 and not F4 because it's joining with Table A?

sunny_talwar · ‎2017-02-24

But that's not how QlikView perform

Step 1: Table A created with fields F1, F2, F3

Step 2: Join B to Table A. Now Table A is F1, F2, F3, & F4

Step3: Join C to Table A (Join will be based on matching field names which are F1, F3, & F4

Anonymous · ‎2017-02-24

When A is joined with B, the first resultant table will become A and is further joined with C which gives me the final resultant table. Right?

Also, the values 15 and 20 are not displayed because the first resultant table (joining A and B) has no sch values in F4?

surendraj · ‎2017-02-24

Yes..of course.

Left join loads full left table and loads matching records from right table.there are no such values(15 and 20) in Left.so it won't consider!!

Anonymous · ‎2017-02-24

It won't consider the corresponding values of F1 and F3 in this case?

sunny_talwar · ‎2017-02-24

Do you have an output you are looking to get based on the three tables? May be we can help you get that because if you do this using the script, you are going to get an output I specified in my first response