Difference between left Keep vs left Join

[#1]

Hi

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.

[#2]

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.

[rohit214]

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;

[rohan_mulay]

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.

[its_anandrjs]

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.

[rohan_mulay]

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.

[SunilChauhan]

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 )

ia

10211

30000

beacuse it has no matching recod in Tab1