topic Add Left Join in QlikView

Add Left Join

Anonymous — Tue, 12 Apr 2016 10:21:55 GMT

Hello All,

I am working on an application which was already developed by someone else. I came across one of the joins condition "Add Left Join", at first I thought it must be "Add Load', but it seems that it is "Add Left Join", I would like to know if it exists or not before going into a discussion with the concerned people. If that particular condition exists can anyone please explain it's working.

Thanks in advance.

Re: Add Left Join

vikasmahajan — Tue, 12 Apr 2016 10:28:51 GMT

TableA :

load * from Sales

left join (TableA)

load * from orders

where condition ;

HTH

Vikas

Re: Add Left Join

sujeetsingh — Tue, 12 Apr 2016 10:33:38 GMT

Use left join simple.

Re: Add Left Join

avinashelite — Tue, 12 Apr 2016 10:40:05 GMT

Is it a comment like Add left join or its a script statement ??

could you please share the script ? so that we can try to identify how its used in the script ??

Re: Add Left Join

avinashelite — Tue, 12 Apr 2016 10:42:57 GMT

I feel they have written it for the Partial reload purpose ...

i.e if you click on the partial reload option from the file menu only the table with the Add left join will get loaded and the other statements will be ignored

Hope this helps you

Re: Add Left Join

Peter_Cammaert — Tue, 12 Apr 2016 10:48:27 GMT

Indeed. The ADD prefix doesn't do anything during a normal script run. However it kicks into action when performing a Partial Reload. ADD tries to load data without first removing the target table. Sort of a switchable CONCATENATE LOAD. Compare this to its counterpart REPLACE which will effectively drop the existing target table during a partial reload, before loading new rows.

Not sure whether this can be sensibly combined with a LEFT JOIN though... Does it work?

Re: Add Left Join

avinashelite — Tue, 12 Apr 2016 10:51:21 GMT

Hi pcammaert‌ , yes its works fine with the left join . Just now I tested with same data it works fine

Re: Add Left Join

Anonymous — Tue, 12 Apr 2016 11:21:29 GMT

Hi Avinash,

Thanks for the quick response. So according to what you say

Add Left join

Load * from..............

and

Left Join

Add Load * from .................

are the same right?

Re: Add Left Join

avinashelite — Tue, 12 Apr 2016 11:25:33 GMT

If you go for normal reload then both are same ...both the statements will execute but if go for Partial reload then only the

Add Left join

Load * from..............

will get executed and rest of the statements will retain the old data

Re: Add Left Join

Anonymous — Tue, 12 Apr 2016 11:55:35 GMT

If we do a partial reload according to my knowledge, only the tables with Add or Replace prefixes will execute ignoring all the other tables. So here will the both Loads execute and return the same output?

Re: Add Left Join

avinashelite — Tue, 12 Apr 2016 12:34:48 GMT

no , since Add keyword is used with the left join ...the table after the add keyword will be loaded in the partial reload ...

Try like this

Temp:

LOAD * Inline

[

Product_ID,Name

1,A

2,B

3,C

];

Add left Join

LOAD * inline

[

Product_ID,Amount

1,100

];

when you hit normal reload both the tables will get loaded

ry like this

//add the 4,D to the temp table and now hit partial reload , only the 2 table will get loaded and the newly added add in the Temp will not be added.

Temp:

LOAD * Inline

[

Product_ID,Name

1,A

2,B

3,C

4,D

];

Add left Join

LOAD * inline

[

Product_ID,Amount

1,100

2,200

];

Re: Add Left Join

Peter_Cammaert — Tue, 12 Apr 2016 13:04:52 GMT

Yes they are equivalent.

ADD, LEFT and JOIN(tablename) are all LOAD statement prefixes. The order in which you specify them is not important. As prefixes generally do, they should be placed before the LOAD keyword.

Re: Add Left Join

Anonymous — Tue, 12 Apr 2016 14:04:29 GMT

Hi Avinash,

Thanks for the detailed reply.