topic Difference between left Keep vs left Join in QlikView

Difference between left Keep vs left Join

Fri, 25 Nov 2011 07:15:46 GMT

I've data like :

Test1:

LOAD * INLINE [

F1,F2,l1,l2

a,1,1,1

b,2,3,3

c,1,2,2

];

Test2:

Left Keep (Test1)

Load * Inline [

F1,F3

a,2

a,3

a,4

b,1

];

- Now i'm taken expression in straight table without dimension as Count(F1) then my result is 3

- But if i use Join the result is 12.

I went to the reference manual..the content says there is no difference between keep and join. Just usage of memory only we will use keep.

Difference between left Keep vs left Join

Fri, 25 Nov 2011 07:30:04 GMT

Hi,

If you have two tables and use join, the result is one logical table, but if you use keep, you keep both tables separately.

Difference between left Keep vs left Join

rohit214 — Fri, 25 Nov 2011 09:24:10 GMT

HI

Left

The join and keep prefixes can be preceded by the prefix left.

If used before join it specifies that a left join should be used. The resulting table will only contain combinations of field values from the raw data tables with a full set of data from the first table.

If used before keep, it specifies that the second raw data table should be reduced to its common intersection with the first table, before being stored in QlikView.

left ( join | keep) [ (tablename ) ](loadstatement |selectstatement )

Examples:

Table1
A	B
1	aa
2	cc
3	ee

Table2
A	C
1	xx
4	yy

QVTable:

select * from table1;

left join select * from table2;

QVTable
A	B	C
1	aa	xx
2	cc
3	ee

QVTab1:

select * from Table1;

QVTab2:

left keep select * from Table2;

QVTab1
A	B
1	aa
2	cc
3	ee

QVTab2
A	C
1	xx

The two tables in the keep example are, of course, associated via A.

tab1:

Load * from file1.csv;

tab2:

load * from file2.csv;

.. .. ..

left keep (tab1) load * from file3.csv;

Re: Difference between left Keep vs left Join

rohan_mulay — Mon, 12 May 2014 06:53:31 GMT

Consider an example:

Test1:

LOAD * INLINE [

ID,Salary,l1,l2

a,1,1,1

b,2,3,3

c,1,2,2

];

Test2:

Left keep (Test1)

Load * Inline [

ID,F3

a,2

a,3

a,4

b,1

];

Refer the screen shot.

Please refer the attached QVW file and try to check the code with Left Join and Left Keep.

Re: Difference between left Keep vs left Join

its_anandrjs — Mon, 12 May 2014 06:57:18 GMT

Basic difference is that when we join two tables we get single one table. But if we use Keep with join then join is perform but we get separate table in our model.

Re: Difference between left Keep vs left Join

rohan_mulay — Mon, 12 May 2014 07:09:54 GMT

But practically we must also know when we should use Left join and left keep. As people knows what is Left keep and Left Join but when they get a real time scenario, they get confused.

Re: Re: Difference between left Keep vs left Join

SunilChauhan — Thu, 16 Oct 2014 07:16:59 GMT

The difference left join and keep is as below

Join: 1 table result

Keep: two table result

Join result is matching from first table

keep result is from from first table and extras rows in second table got droped.

see the attched file where i have two tables and after left join one row got deleted from second table(see excel )

10211

30000

beacuse it has no matching recod in Tab1