When Use Keep When Use Join

[nihhalmca]

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.

[its_anandrjs]

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

[vardhancse]

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

[its_anandrjs]

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

[nihhalmca]

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.

[nihhalmca]

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.

[vardhancse]

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

[#8]

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

[jagan]

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.

[Peter_Cammaert]

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