When Use Keep When Use Join

[nihhalmca]

Hi All,

I have knowledge what is KEEP and JOIN. I am trying to know which scenario will

use KEEP (ex: LEFT KEEP) and which scenario will use JOIN (ex: LEFT JOIN).

Can you people share with examples.

Regards,

Nihhal.

[vardhancse]

Keep

The keep prefix is similar to the Join prefix. Just as the join prefix, it compares the loaded table with an existing named table or the last previously created data table.

But instead of joining the loaded table with an existing table, it 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 comparison made is equivalent to a natural join made over all the common fields, i.e. the same way as in a corresponding join. However, the two tables are not joined and will be kept in QlikView as two separately named tables.

Note!
The explicit Join prefix in QlikView script language performs a full join of the two tables. The result is one table. In many cases such joins will result in very large tables. One of the main features of QlikView is its ability to make associations between multiple tables instead of joining them, which greatly reduces memory usage, increases processing speed and offers enormous flexibility. Explicit joins should therefore generally be avoided in QlikView scripts. The keep functionality was designed to reduce the number of cases where explicit joins needs to be used.

The keep prefix must be preceded by one of the prefixes Inner, Left or Right. The syntax is:

(inner | left | right) keep[ (tablename ) ]( loadstatement | selectstatement )

Example:

Inner Keep LOAD * FROM abc.csv;

Left Keep SQL SELECT * FROM table1;

tab1:

LOAD * FROM file1.csv;

tab2:

LOAD * FROM file2.csv;

.. .. ..

Left keep (tab1) LOAD * FROM file3.csv;

[Anonymous]

Hi Nihhal,

May be this post helps you:

Difference between keep and joins with example

regards

Neetha

[nihhalmca]

Thanks for response.

Plz see my first post. I have knowledge on what is KEEP and JOIN, i am looking for scenario when use keep and when use join. I am expecting something is related to jagan's answer.

[vardhancse]

Both are same, but the only difference was representation what we see in data model.

Join: in terms of representation all tables will be in to one single table

Keep: in terms of representation, we can see separate tables.

But result data will be the same

[nihhalmca]

Thanks Jagan.

[Anonymous]

Hi Nihhhal. even if it is a old question and i already answer in another one , i pasted here my solution.

Like Jagar said "If you have one to many or many to many relationship then we can use KEEP, because if we use join the records will be duplicated."

A simple example of that would be:

TableA:

load * inline

[

Key,A

1,A1

2,A2

3,A3

];

TableC:

load * inline

[

Key,C

1,C1

1,C11

1,C111

2,C2

4,C4

];

that give use to tables .

When we use a inner joing we are having that:

where of course we are duplicating the A1 value for nothing.

If we use a Keep.

Hope it helps , Enrique

[kurtwert71]

Hi. I have searched the boards for an hour, hoping to find this answered, but no luck, so here I go...

I have a table with 250M sales records,.

I have another table with 9000 devices that get sold. The two tables have "prod_nm" in common.

I load the first table, then I run script to create several calculated dimensions using data in the loaded "resident" table and then I save the result as a new table and drop the original table. So far, so good. 

Then I do "left keep" to load the second table, so everything works and the file size is smaller than a "join" which would merge all the device data with all the sales data and it gets huge.

The problem is that I want to build a calculated dimension that uses data from both tables. Such as "if customer is new and device is phone, then "new / phone" or if customer is old and device is tablet then "old / tablet". It isn't quite that simple, but I hope you get the idea. The point is that I want to create a dimension to group things in a report, and the elements I need are in two tables. 

It won't let me include the logic in my original load script, because it tells me that it can't find the data in my "keep" table. I tried running that part first, but I don't see a way in the script where I refer to the Resident table to tell it to also look in the second "keep" table. Is there any way to do that? I know I could do it with an extra step and just "Join" to my table of models, but that would create a giant table that I do not want or need...