Joins and Lookups

[hic]

This document concerns data modelling and joins

• What is the difference between a join using the join prefix and a join using a SELECT statement?
• When should you join and when shouldn't you? Performance considerations?
• Are there alternatives to joins? (Yes! Applymap!)
• What does the Keep prefix do?

HIC

See also my blog post on the same subject: To Join or not to Join.

[#2]

Excellent as usual

Fabrice

[MK_QSL]

Excellent Job Done... Very Useful.

[#4]

Muy bueno.

Gracias

[#5]

Simple And Clear

[Anonymous]

great job !

[#7]

Thank you very much

[#8]

Hi

1. I made two inline tables just to test the keep function. Left and inner keep works fine, but a pure keep does not work?   

2. I am very confused, I thought that star schemes where recommended with Qlikview, but here normalization is recommended in most cases?

3. What happens in a join if only some of the data in the specific rows are the same?    

[hic]

1. If you don't specify which type of Join, then it is "Outer". The same logic applies for Keep, but since "Outer Keep" doesn't make sense....

2. It is a trade-off. Snowflake scheme is in my opinion easiest to maintain and should therefore be used. But for large data sets, a star scheme outperforms the snowflake scheme. So, star scheme may then be the better option.

3. Depends on whether you use Outer, Left, Right or Inner Join.

HIC

[#10]

thx:

1. ah okay, of course it does not makes sense then    Since it accept "keep" as single word in the code, marked as blue, I thought it would do something. So I guess it only evaluates single words.

2. I thought normalization was for large data set and complex systems. Does the star and snowflake only make sense with a fact/dimensional approach? 

3. I will do some more testing   

[#11]

very informative - thanks for sharing