topic Confused with join in App Development

Confused with join

Fri, 15 Jan 2016 14:29:05 GMT

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!

Re: Confused with join

maxgro — Fri, 15 Jan 2016 18:03:55 GMT

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;

Re: Confused with join

gferran — Fri, 15 Jan 2016 19:26:03 GMT

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.

Re: Confused with join

Fri, 15 Jan 2016 19:51:37 GMT

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

Re: Confused with join

maxgro — Fri, 15 Jan 2016 20:04:19 GMT

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 )