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

[its_anandrjs]

You can refer this nice documents for the Keep

Joins and Lookups

[Anonymous]

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

[#4]

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

[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. 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.

[charishma01]

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.