Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Start with this thread (more to find when searching the forum for JOIN and KEEP):
Asma,
Don't act as Lazy, Follow Swuehl manner. I followed same one only...!
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
Hi Andrew,
Thanks for the detailed explanation!