Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Confused with join

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:

KeyColumn1Column2Column3Column4
A141144
A252255
A363366

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!

1 Solution

Accepted Solutions
maxgro
MVP
MVP


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;

1.png

View solution in original post

4 Replies
maxgro
MVP
MVP


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;

1.png

gferran
Partner - Contributor III
Partner - Contributor III

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.

Not applicable
Author

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
MVP
MVP

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 )