Confused with join

[#1]

Hi There,

I'm really confused about the Join function in scripts.   I've read several blog entries and the help topics but I'm still not getting it.

Take this script:

Table1:

Load  * Inline [

Key, Column1 , Column2

A,1,4

A,2,5

A,3,6

];

Table2:

Load  * Inline [

Key, Column3, Column4

A,11,44

A,22,55

A,33,66

];

Join (Table1)

Load

*

Resident Table2;

Drop table Table2;

I would expect the product of this to be a nice table something like this:

Key	Column1	Column2	Column3	Column4
A	1	4	11	44
A	2	5	22	55
A	3	6	33	66

But instead I get something akin to a Cartesian join.    This has me stumped.  Both table share a key field (Key!) why am I getting duplicate rows?

Help!

[maxgro · Accepted solution]

because the common field, used in the join, is Key and, a join is used for combining fields from two tables (or more) by using values common to each

for every A in the first table, with the join you get 3 rows in the second table --> 9 rows

try this if you only want 3 rows

Table1:

Load  * Inline [

Key, Column1 , Column2

A,1,4

AA,2,5

AAA,3,6

];

Table2:

Load  * Inline [

Key, Column3, Column4

A,11,44

AA,22,55

AAA,33,66

];

inner Join (Table1)

Load

*

Resident Table2;

Drop table Table2;

[gferran]

The default join is the inner join. It combines each key with values equals in the other table.

As maxgro said you need to have diferent values for each key value. If you don't you obtain a cartesian join because each value in  the first table combines with all values in the second.

[#4]

Thanks maxgro that's helped solidify it in my mind.  The data in the field must be unique. 

I've checked my app and it is (in principle) unique.   The common field across both tables I'm trying to join is a combination of emplid and date that is returning duplicates.

However, it turns out that the data quality is an issue (there are some unintentional overlapping dates) meaning the field isn't actually unique at all.

Thanks once again

[maxgro]

AFAIK the default join in Qlik is the outer because, below is from the Qlik help, the outer is optional

The explicit Join prefix can be preceded by the prefix outer in order to specify an outer join. In an outer join all combinations between the two tables are generated. The resulting table will thus contain combinations of field values from the raw data tables where the linking field values are represented in one or both tables. The outer keyword is optional.

outer join [ (tablename ) ](loadstatement |selectstatement )