Incorrect JOIN or LOAD feature

[#1]

Hello to all!

This is my first entry

Today we look at the unusual behavior (at least in book is nothing written).

To start, download the attached file "Bad_Join.qvw" and open (project written in 11.20.12235.0 SR5 64-bit version).

Step 1

We have two tables "SALESMAN" and "SALES", and salesman Ivan added 2 times by mistake. Our task is to combine the table "SALESMAN" with "SALES".

Step 2

Use "LEFT JOIN". Table's were crossed and Ivan received more sales of "Ushanka" and "Balalaika" than actually. We need a "DISTINCT".

Step 3

Wait, what happened to the 20 bottles of "Vodka" and 20 pieces of "Balalaika"??? Vladimir and Ivan very are angry because of this!!!

Step 4

Add a temporary table "SALESMAN_TMP", where use "DISTINCT". And then use "LEFT JOIN" like in Step 2. Vladimir and Ivan are no longer are mad at us.

And what in the end?

Prefix "DISTINCT" in Step 3 applies not only to "SALESMAN", but on "SALES" and "SALESMAN" together!

Be careful with that!

I think, that this is a bug, and not a feature.

With Best Regards From Russia - Anton Sukharev.

[rbecher]

Hi Anton,

DISTINCT in QlikView works a bit different than in SQL databases. It will effect all loaded data of the table, not only the data of the actual LOAD statement with the given DISTINCT statement. So, it's rather a behavior than a bug..

- Ralf

[#3]

Hello, Ralf.

If this a behavior, why he did not documented? This is a very dangerous behavior.

[rbecher]

Indeed, life can be dangerous..