Difference b/w Left keep and exists()

[#1]

Hi,

What is the difference b/w using a 'Left join' , 'exists()' and 'Left keep'?
In what way there are different from each other.I guess 'Left keep' and 'exists()' will do the same work.Please clarify it.

[nagaiank]

The use of 'Keep' prefix in QlikView is explained in detail with examples in the QlikView Reference manual.

The 'Left Join' in QlikView is very much like 'Left Join' in SQL.

For 'exists()' function also, see the Reference Manual.

[erichshiino]

I'd say that you can get the same results but maybe you can get a better performance with one of them.

First, if you have a small control table A, and want to use it to filter a table B.

If you load A and then use B with a left keep, you first loaded all the registers in A.

If you had two million registers in A and used B to get 100,000 you actually loaded all of them first.

If you first load B and then use Exists(), QV may check all the registers, but only the selected ones will be loaded.

If can reach the limits of memory in your system in the first case, but not in the second.

Also, you could use not exists() which may not be so easy to achieve this the left keep...

Maybe you have a different scenario where we can think about the best approach for you...

Hope this helps for now,

Erich

[#4]

Thanks alot Shiino. got it.

[#5]

Saw this post when researching a similar question. I tested performance of the load for all three and the run times are almost (if not) exactly the same. From what I have found, the big differences are: JOIN - combines all the tables as one denormalized table, risking very large tables. KEEP - reduces the data and does not denormalize; the problem I found with keep was that it does not seem possible to reduce multiple tables referencing multiple keys from one (Fact) table. EXISTS - reduces data, does not denormalize and allows for multiple tables to be associated to the same (Fact) table. So for my purposes, EXISTS was the correct choice.

[#6]

Correction: For KEEP you can specific the source table you want the "keep" statement to reference: "left keep (tablename)". When coded this way, there does not appear there is no difference in the end result between KEEP and EXISTS. "The Reference Manual" (Version 10) does not explicitly state that the reference to the source table can be made.

[#7]

exists() : you can use a formula and fields can have differents names.

It's just for 1 field, If you want to do that for many fields, you need to repeat the condition