topic Re: Doubt in Left join in QlikView

Doubt in Left join

Anonymous — Fri, 24 Feb 2017 13:49:34 GMT

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

Re: Doubt in Left join

sunny_talwar — Fri, 24 Feb 2017 13:51:44 GMT

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

Re: Doubt in Left join

Anonymous — Fri, 24 Feb 2017 13:57:15 GMT

Hi Sunny

Why the table C will have no impact?

Re: Doubt in Left join

sunny_talwar — Fri, 24 Feb 2017 14:00:49 GMT

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)

Re: Doubt in Left join

Anonymous — Fri, 24 Feb 2017 14:09:05 GMT

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?

Re: Doubt in Left join

sunny_talwar — Fri, 24 Feb 2017 14:11:58 GMT

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

Re: Doubt in Left join

Anonymous — Fri, 24 Feb 2017 14:39:57 GMT

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?

Re: Doubt in Left join

surendraj — Fri, 24 Feb 2017 14:51:29 GMT

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!!

Re: Doubt in Left join

Anonymous — Fri, 24 Feb 2017 15:04:06 GMT

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

Re: Doubt in Left join

sunny_talwar — Fri, 24 Feb 2017 15:06:45 GMT

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

Re: Doubt in Left join

Anonymous — Fri, 24 Feb 2017 15:20:45 GMT

The final output which i am getting is exactly the same which you gave but when I look at the resultant table I can't see all the values of F4 (including 15 and 20) since the corresponding values of F1 and F3 are matching.

Could you please elaborate on this?

Re: Doubt in Left join

sunny_talwar — Fri, 24 Feb 2017 15:31:12 GMT

If I leave the second join and run this script

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

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;

Re: Doubt in Left join

Anonymous — Fri, 24 Feb 2017 15:50:15 GMT

That explanation helped me understand the whole process.

Thank you.

Re: Doubt in Left join

sunny_talwar — Fri, 24 Feb 2017 15:56:37 GMT

Awesome, I am glad it helped. Please close the thread by marking correct and helpful responses.

Best,

Sunny

Re: Doubt in Left join

Anonymous — Fri, 24 Feb 2017 16:11:33 GMT

Sure.

Re: Doubt in Left join

sunny_talwar — Fri, 24 Feb 2017 16:13:00 GMT

Thank you