Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
riyazasma1009
Creator
Creator

Join vs Keep

Hi All,

Can someone please explain me with use cases as to under what conditions should join or keep be used.

I am aware that join creates a single table as output and keep stores the result in two different tables in the data model. But I am not aware of any practical use case where join or keep can be used.

Can someone please explain with some practical use cases as to under what conditions join or keep should be used.

Thanks,

Asma

1 Solution

Accepted Solutions
effinty2112
Master
Master

Hi Asma,

Here's a practical use for KEEP. Imagine you have a source table of customers and a source table of orders that you don't want to combine into one table in your QV model.

You want to load all orders for product ABC bought by customers from Country XYZ.

Let's 'keep' things simple and say there is only one product per order.

If you try

Customers:

LOAD

CustomerID,

.

.

Resident customers WHERE Country = XYZ;

Orders:

LOAD

CustomerID,

.

.

Resident Orders WHERE Product = ABC;

This will load Customers who may not have bought ABC and you may have orders for product ABC with no corresponding Customer record because these orders were bought from a country other than XYZ.

Instead if you try

Customers:

LOAD

CustomerID,

.

.

Resident customers WHERE Country = XYZ;

Orders:

Inner Keep(Customers)

LOAD

CustomerID,

.

.

Resident Orders WHERE Product = ABC;

Qlikview will drop records from Customers who did not order product ABC and will only load Orders placed by Customers from XYZ.

It's exactly like an inner join except the two tables are not combined in the data model.

You can replace Inner Keep(Customers) with Left Keep(Customers) then no customer records will be removed and only orders for ABC associated with these customers will load.

Right Keep(Customers) will load all orders for product ABC but only customers who bought ABC will be remain in the Customers table.

All this is analogous to Inner, Left and Right Joins except the tables are kept separate.

Cheers

Andrew

View solution in original post

4 Replies
swuehl
MVP
MVP

Start with this thread (more to find when searching the forum for JOIN and KEEP):

Understanding Join, Keep and Concatenate

Re: when to use Join and when to use Keep ?

Anil_Babu_Samineni

Asma,

Don't act as Lazy, Follow Swuehl manner. I followed same one only...!

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
effinty2112
Master
Master

Hi Asma,

Here's a practical use for KEEP. Imagine you have a source table of customers and a source table of orders that you don't want to combine into one table in your QV model.

You want to load all orders for product ABC bought by customers from Country XYZ.

Let's 'keep' things simple and say there is only one product per order.

If you try

Customers:

LOAD

CustomerID,

.

.

Resident customers WHERE Country = XYZ;

Orders:

LOAD

CustomerID,

.

.

Resident Orders WHERE Product = ABC;

This will load Customers who may not have bought ABC and you may have orders for product ABC with no corresponding Customer record because these orders were bought from a country other than XYZ.

Instead if you try

Customers:

LOAD

CustomerID,

.

.

Resident customers WHERE Country = XYZ;

Orders:

Inner Keep(Customers)

LOAD

CustomerID,

.

.

Resident Orders WHERE Product = ABC;

Qlikview will drop records from Customers who did not order product ABC and will only load Orders placed by Customers from XYZ.

It's exactly like an inner join except the two tables are not combined in the data model.

You can replace Inner Keep(Customers) with Left Keep(Customers) then no customer records will be removed and only orders for ABC associated with these customers will load.

Right Keep(Customers) will load all orders for product ABC but only customers who bought ABC will be remain in the Customers table.

All this is analogous to Inner, Left and Right Joins except the tables are kept separate.

Cheers

Andrew

riyazasma1009
Creator
Creator
Author

Hi Andrew,

Thanks for the detailed explanation!