topic Re: Join vs Keep in QlikView

Join vs Keep

riyazasma1009 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Join vs Keep

swuehl — Tue, 14 Jun 2016 13:13:54 GMT

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 ?

Re: Join vs Keep

Anil_Babu_Samineni — Tue, 14 Jun 2016 13:37:38 GMT

Asma,

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

Re: Join vs Keep

effinty2112 — Tue, 14 Jun 2016 14:25:59 GMT

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

Re: Join vs Keep

riyazasma1009 — Thu, 16 Jun 2016 06:58:18 GMT

Hi Andrew,

Thanks for the detailed explanation!