topic Re: Difference between Join and Keep in QlikView

Difference between Join and Keep

Mon, 12 May 2014 03:33:50 GMT

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

Re: Difference between Join and Keep

jagan — Mon, 12 May 2014 03:37:18 GMT

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.

Re: Difference between Join and Keep

sujeetsingh — Mon, 12 May 2014 04:09:07 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 Join and Keep

Anonymous — Mon, 12 May 2014 04:23:10 GMT

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:

Re: Difference between Join and Keep

amit_saini — Mon, 12 May 2014 04:32:07 GMT

Hi Anushree,

Please see the attachment.

Thanks,

Re: Difference between Join and Keep

Mon, 12 May 2014 04:59:20 GMT

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

Re: Difference between Join and Keep

Mon, 12 May 2014 06:11:10 GMT

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.

Re: Difference between Join and Keep

anbu1984 — Mon, 12 May 2014 06:34:53 GMT

Understanding Join, Keep and Concatenate

Re: Difference between Join and Keep

MuraliPrasath — Mon, 12 May 2014 06:35:02 GMT

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

Re: Difference between Join and Keep

Mon, 12 May 2014 06:38:24 GMT

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

Re: Difference between Join and Keep

its_anandrjs — Mon, 12 May 2014 07:25:29 GMT

You can refer this nice documents for the Keep

Joins and Lookups

Re: Difference between Join and Keep

Anonymous — Mon, 12 May 2014 07:31:11 GMT

Hi,

MAPPING LOAD together with the APPLYMAP function is used to insert fields from one table to a second table without the need of storing two tables. This is done to reduce the amount of data being loaded in a qlikview document.

Note: Mapping load tables can only contain two fields. The first field is the key field used to search between two tables using the APPLYMAP function. The second is the field you wish to transfer to the second table.

Example:

Firstly we have the Orders table which contains details about the placed orders. One field in the Orders table is the EmployeeID indicating which Employee entered the order.

The details about the Employees such as Name, Title, Location etc can be found in a separate table called Employees. We wish to include the Name field from the Employees table, however we do not wish to load the employee data into the Qlikview application. This could be due to the size of the employee’s table or security reasons not to include the details of the employees in the application.

We could load just the EmployeeID and Name to fields from the Employeestable and link to the Orders table using the EmployeeID as the key field, however in this example we choose to use the MAPPING LOAD andAPPLYMAP functionality.

Step 1: First the Mapping Load to load the Employees.

Step 2: Load the Orders Table, and use the APPLYMAPfunction to include the Employee Name.

The syntax for the APPLYMAP function is:

applymap( ‘mapname’, expr [ , defaultexpr ] )

The first parameters to state whichmapping table you wish to use. In our example Employees. Single quotes must be placed around the mapping table name.
The second parameters states which field in the Orders table is used to link with the MAPPING LOAD table.
An optional third parameter can be included to express the value that will be returned if there is no match in the mapping load table. If no default value is given, the value will be returned as is.

So the script for the Orders table looks like:

In our example, if the EmployeeID is known in the mapping table, then theEmployeeName field would contain the value of the field Name from the Employees table.

In the (unlikely) case that there was an Employee who placed the Order, but not known in the Employees table, then the EmployeeName field would contain the text ‘Unknown’.

Once the script has completely loaded, the mapping table Employees is automatically deleted and not stored in the Qlikview file.

Regards

Ankita

Re: Difference between Join and Keep

Mon, 12 May 2014 09:51:19 GMT

Thank you Ankita, but can u please explain with inline load,is it possible? if it is not possible explain withouit qualify statement.and also look up.

Thank you

Rafi

Re: Difference between Join and Keep

rohan_mulay — Mon, 12 May 2014 09:59:54 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. Check the explanation in the screen shot below.

Please refer the application for above example and check the result by using left join and left keep.

Re: Difference between Join and Keep

charishma01 — Thu, 07 Jul 2022 08:44:29 GMT

HI,

JOIN AND KEEP both has same functionality the only difference between two is join combines two or more tables in single table we can see output only one table.

keep combines two r more tables (Perform the join) but keeping both the tables separate.

topic Re: Difference between Join and Keep in QlikView

Difference between Join and Keep

Re: Difference between Join and Keep

Re: Difference between Join and Keep

HI

Left

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

Examples:

Re: Difference between Join and Keep

Re: Difference between Join and Keep

Re: Difference between Join and Keep

Re: Difference between Join and Keep

Re: Difference between Join and Keep

Re: Difference between Join and Keep

Join

Keep

LEFT KEEP

RIGHT KEEP

INNER KEEP

Re: Difference between Join and Keep

Re: Difference between Join and Keep

Re: Difference between Join and Keep

Step 2: Load the Orders Table, and use the APPLYMAPfunction to include the Employee Name.

Re: Difference between Join and Keep

Re: Difference between Join and Keep

Re: Difference between Join and Keep