Difference between Join and Keep

[#1]

Hi,

I am new to qlikview,can anyone please let me know the difference between Join and Keep and also in what circumstances each of them are used with an example

[jagan]

Hi,

Join - will join two tables and convert it to one table.

Keep - will implement join logic on two tables and maintains two tables separately.

Refer Qlikview Help file (F1) , it has very good examples.

Regards,

Jagan.

[sujeetsingh]

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;

[Anonymous]

Hi,

JOIN:
It is used to join the table that is being loaded to a previously loaded table. The two tables are joined using a natural join in a single table, this means that the columns in both tables are compared and the join is made over those columns that have the same column names. This means that if multiple columns are shared between tables, the match will be made over the distinct combinations of those columns.
By default, QlikView performs an outer join. This means that the rows for both tables are included in the resulting table. When rows do not have a corresponding row in the other table, the missing columns are assigned null values.

Example:
Table1:
LOAD * INLINE
[
A, B, C
1, 1, 1
2, 2, 2
3, 3, 3
];
JOIN
LOAD * INLINE
[
B, C, D
2, 2, 2
3, 3, 3
5, 5, 5
];

The resulting table will be:

The JOIN statement can be prefixed with the statements INNER, OUTER, LEFT, and RIGHT, which performs an inner, outer, left, or right join respectively. INNER JOIN: Only rows that can be matched between both tables will be kept
in the result.

• OUTER JOIN: All rows will be kept in the result, rows that do not have a corresponding value in the other table will get null values for the fields that are unique to that table. When no prefix is specified, this is the default join
type that will be used.

• LEFT JOIN: All rows from the first table and those rows from the second table that have a corresponding key in the first table, will be included in the result. When no match is found, null values will be shown for the columns
that are unique to the second table.

• RIGHT JOIN: All rows from the second table and those rows from the first table which have a corresponding key in the second table, will be included in the result. When no match is found, null values will be shown for the
columns that are unique to the first table.

KEEP
It works in the same way that the JOIN statement does, with a small difference. Instead of joining the result in a single table, the KEEP statement keeps both original tables and filters (keeps) rows in one table based on matching
rows in another table. The same logic for INNER, OUTER, LEFT, and RIGHT KEEP applies here as did with the JOIN statement.

EXAMPLE

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
];

The resulting table will be:

[amit_saini]

Hi Anushree,

Please see the attachment.

Thanks,

AS

[#6]

Hi Anushree

The keep prefix between two select statements has the effect of reducing one or both of the two tables before they are stored in QlikView.

Thanks

MV

[#7]

Hi,

Join:  if will join two tables the O/P result set will convert it to one table.

Keep: if will keep means, it will implement join logic on two tables and O/P result set maintains two tables separately.

Examples:

Join:

Sample:

Table1:

LOAD * INLINE

[

A, B, C

aaa,bbb,ccc

abc, bcd, cde

def,fgh ,ijk

];

JOIN

LOAD * INLINE

[

B, C, D

xyx, pqr, uvw

nmo, rst, mmm

nnn, ooo, ppp

];

Keep:

Sample:

Table1:

LOAD * INLINE

[

A, B, C

aaa,bbb,ccc

abc,bcd,cde

def,fgh ,ijk

];

Table2:

Inner Keep

LOAD * INLINE

[

B, D, E

xyx, pqr, uvw

nmo, rst, mmm

nnn, ooo, ppp

];

Please refer Qlikview Help File, it has very good explanation with examples.

[anbu1984]

Understanding Join, Keep and Concatenate

[MuraliPrasath]

Join

The purpose of JOIN is to add columns to a table. As a side effect, there could be additional rows.  To join properly, the tables must have at least one common field.

Table: A                    Table B:                         Table C:

A   B C                      A  D  E          Left Join:  A  B  C  D  E

1    0 0                      1  5  6                            1   0  0  5  6

1    0 1                      1  7  3                            1   0  1  5 6                    

0    1 0                                                           1   0   0  7  3 ... and so on

Keep

The keep prefix between two load or select statements has the effect of reducing one or both of the two tables before they are stored in QlikView, based on the intersection of table data. The keep keyword must always be preceded by one of the prefixes inner,left or right. The selection of records from the tables is made in the same way as in a corresponding join.

However, the two tables are not joined and will be stored in QlikView as two separately named tables.

LEFT KEEP
Table1	Table2
Key	A	Key	C
1	A1	1	C1
2	A2	2	C2
3	A3	3	-

RIGHT KEEP
Table1	Table2
Key	A	Key	C
1	A1	1	C1
2	A2	2	C2
4	-	4	C4

INNER KEEP
Table1	Table2
Key	A	Key	C
1	A1	1	C1
2	A2	2	C2

[#10]

Hi ankita good example by join and keep thank you,

may you small fever for me can please explain look up, mapping load,apply map

am waiting for your reply

Thank you

Rafi