topic Re: Left Keep Vs Left Join in QlikView

When Use Keep When Use Join

nihhalmca — Wed, 10 Dec 2014 06:33:06 GMT

Hi All,

I have knowledge what is KEEP and JOIN. I am trying to know which scenario will

use KEEP (ex: LEFT KEEP) and which scenario will use JOIN (ex: LEFT JOIN).

Can you people share with examples.

Regards,

Nihhal.

Re: Left Keep Vs Left Join

its_anandrjs — Wed, 10 Dec 2014 06:39:13 GMT

Hi,

When any join performs between two tables you get single table but in the Keep join join is perform but you have two tables.

Read some of this

Joins and Lookups

Some Qlikview Joins best documents

Understanding Join, Keep and Concatenate

Different Join Functions in Qlikview

Regards

Anand

Re: Left Keep Vs Left Join

vardhancse — Wed, 10 Dec 2014 06:41:47 GMT

Hi,

Left Keep:

Table1:

LOAD * INLINE

[

A, B, C

1, 1, 1

2, 2, 2

3, 3, 3

];

Table2:

LEFT KEEP (Table1)

LOAD * INLINE

[

B, C, D

2, 2, 2

3, 3, 3

5, 5, 5

];

Result:

2 separate tables

Table 1:

A B C

1 1 1

2 2 2

3 3 3

Table 2:

B C D

2 2 2

3 3 3

Left Join:

Table1:

LOAD * INLINE

[

A, B, C

1, 1, 1

2, 2, 2

3, 3, 3

];

LEFT JOIN(Table1)

LOAD * INLINE

[

B, C, D

2, 2, 2

3, 3, 3

5, 5, 5

];

Result:

1 single table

A B C D

1 1 1

2 2 2 2

3 3 3 3

Re: Left Keep Vs Left Join

its_anandrjs — Wed, 10 Dec 2014 06:46:46 GMT

Hi,

Assume this example

LOAD * Inline

[

ID,Value

A,20

B,30

D,45

E,50

];

left Keep

LOAD * Inline

[

ID,Amt

A,203

B,304

C,105

];

After this you get

LOAD * Inline

[

ID,Value

A,20

B,30

D,45

E,50

];

left Join

LOAD * Inline

[

ID,Amt

A,203

B,304

C,105

];

Result will be

Regards

Anand

Re: Left Keep Vs Left Join

nihhalmca — Wed, 10 Dec 2014 06:47:05 GMT

Thanks for your reply,

which scenarios we use left keep. can you give one example where we have to use only left keep.

Regards,

Nihhal.

Re: Left Keep Vs Left Join

nihhalmca — Wed, 10 Dec 2014 06:47:19 GMT

Thanks for your reply,

which scenarios we use left keep. can you give one example where we have to use only left keep.

Regards,

Nihhal.

Re: Left Keep Vs Left Join

vardhancse — Wed, 10 Dec 2014 06:49:57 GMT

functionality wise both are same, only difference is the representation in data model.

keep we will see in to 2 separate tables, join we will see in one single table

Re: Left Keep Vs Left Join

Wed, 10 Dec 2014 06:55:49 GMT

When you left join many table to the master table them it is difficult sometimes to realize which table that particular data is coming from. While when left keep we get to know which data is coming from which table.

Thanks,

Ayush

Re: Left Keep Vs Left Join

jagan — Wed, 10 Dec 2014 07:22:16 GMT

Hi Nihhal,

In simple terms when you need to join two tables physically then use Left join (Two tables become one table), if you want to apply join logic and want to keep the two tables then use Left Keep.

Join works similiar to the SQL.

Generally left join used to load master records which are there in Transaction tables, please check below example

Transaction:

LOAD

INLINE [

CountryID, Sales

1, 1000

2, 3000

];

CountryMaster:

LEFT KEEP (Transaction)

LOAD

CountryID,

Country

INLINE [

CountryID, Country

1, USA

2, India

3, UK ];

After executing this script you will find two tables Transaction and CountryMaster, and CountryMaster table is having only two records with country USA and India, since UK has no sales in Transaction table.

Load UK in master table is of no use, also it is difficult if there many countries for users to select countries with sales.

Hope this helps you.

Regards,

Jagan.

Re: Left Keep Vs Left Join

Peter_Cammaert — Wed, 10 Dec 2014 07:56:50 GMT

Whenever you want to reduce a table (e.g. products) to all entries that occur in another table (e.g. Orders), but without joining the two together.

Peter

Re: Left Keep Vs Left Join

nihhalmca — Fri, 12 Dec 2014 16:40:17 GMT

Hi Jagan,

I tried your examples result as

LEFT KEEP

Transaction:

Country ID, Sales

1, 1000

2, 3000

Countrymaster:

Country ID, Country

1, USA

2, INDIA

3, UK

LEFT JOIN

Country ID, Sales, Country

1, 1000, USA

2, 3000, INDIA

I did not understand difference between LEFT KEEP and LEFT JOIN? (Just difference no of tables)

Can you give brief example if possible.

Thanks,

Nihhal.

Re: Left Keep Vs Left Join

its_anandrjs — Fri, 12 Dec 2014 17:10:00 GMT

Hi Nikhil,

When you use the left Join functionality as Jagan give example by expression

Transaction:
LOAD * INLINE [
CountryID, Sales
1, 1000
2, 3000 ];

CountryMaster:
LEFT JOIN (Transaction)
LOAD * INLINE [
CountryID, Country
1, USA
2, India
3, UK ];

You get result single table with data

And if you use Left Keep

Transaction:
LOAD * INLINE [
CountryID, Sales
1, 1000
2, 3000 ];

CountryMaster:
LEFT KEEP (Transaction)
LOAD * INLINE [
CountryID, Country
1, USA
2, India
3, UK ];

You get two tables and join performs but data of same records in the second table that is CountryMaster

Hope this helps

Regards

Anand

Re: Left Keep Vs Left Join

nihhalmca — Thu, 18 Dec 2014 11:27:47 GMT

Hi Anand thanks for writing.

As per above conversations, only difference is number of tables?

I have read 'KEEP' as one of the performance technique.

Regards,

Nihhal.

Re: Left Keep Vs Left Join

Thu, 18 Dec 2014 11:36:54 GMT

HI,

In simple words

-->>Left keep is same like left join ,it does the join but it keeps the table separate in data model.

-->>Left join, joins the two tables and make it one table.

thats the only diffrence.and this technique is helpful as per scenarios that anybody can get.

thanks

Sudhanshu shrivas

Re: Left Keep Vs Left Join

nihhalmca — Thu, 18 Dec 2014 12:52:34 GMT

Hi Shrivas,

Thanks for reply.

Can you share which scenario we go for JOIN and which scenario we go for KEEP if possible.

Regards,

Nihhal.

Re: Left Keep Vs Left Join

jagan — Thu, 18 Dec 2014 13:27:45 GMT

HI Nihhal,

If you have one to many or many to many relationship then we can use KEEP, because if we use join the records will be duplicated.

Regards,

Jagan.

Re: Left Keep Vs Left Join

Anonymous — Thu, 18 Dec 2014 14:28:15 GMT

Hi,

left keep and left join gives the same output.the only difference is :

Left Keep : keeps the table separate in data model.

Left Join : merges the tables that are joined

Regards

Neetha

Re: Left Keep Vs Left Join

nihhalmca — Fri, 14 Aug 2015 08:56:28 GMT

Hi Jagan,

Thanks for response. It seems to help me if you share small example.

Regards,

Nihhal.

Re: Left Keep Vs Left Join

vardhancse — Fri, 14 Aug 2015 09:04:16 GMT

Hi,

You can close the thread if you got required information.

Regards,

Sasi

Re: Left Keep Vs Left Join

nihhalmca — Fri, 14 Aug 2015 09:07:16 GMT

Please see my last reply on this post. So i am waiting for response from jagan.