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

Problem with Left Join

Hello,

I need to add 2 left join to my main table. But the problem : only the first join is did, never the second.

here my code :

 

TABLE1:

LOAD * INLINE [
KEY, Year, Material, data1
68, 07.2014, 111111, 100
68, 07.2014, 222222, 101
68, 07.2014, 333333, 102
70, 07.2014, 444444, 500
]
;

 

left join (TABLE1)
LOAD * inline [
KEY, Year, Material, data2
70, 07.2014, 444444, 5000
]
;

left join (TABLE1)
LOAD * inline [
KEY, Year, Material, data2
68, 07.2014, 111111, 1000
68, 07.2014, 333333, 1020
]
;

the result must be (that I want) :

KEY, Year, Material, data1,     data2

68, 07.2014, 111111, 100,        1000

68, 07.2014, 222222, 101,         -

68, 07.2014, 333333, 102,        1020

70, 07.2014, 444444, 500,        5000

But I have :

KEY, Year, Material, data1,     data2

68, 07.2014, 111111, 100,        -

68, 07.2014, 222222, 101,        -

68, 07.2014, 333333, 102,        -

70, 07.2014, 444444, 500,        5000

or (depending of the first left)

KEY, Year, Material, data1,     data2

68, 07.2014, 111111, 100,        1000

68, 07.2014, 222222, 101,         -

68, 07.2014, 333333, 102,        1020

70, 07.2014, 444444, 500,         -

why it's not possible to do 2 left join (because I have 2 Excel files to add to my QVD). It's a bug or a QV logic ?

Thanks in advance for your help.

1 Solution

Accepted Solutions
nagaiank
Specialist III
Specialist III

Try the following script instead:

Table1:

LOAD * INLINE [
KEY, Year, Material, data1
68, 07.2014, 111111, 100
68, 07.2014, 222222, 101
68, 07.2014, 333333, 102
70, 07.2014, 444444, 500
]
;
Table2:
LOAD * inline [
KEY, Year, Material, data2
70, 07.2014, 444444, 5000
]
;
Concatenate
LOAD * inline [
KEY, Year, Material, data2
68, 07.2014, 111111, 1000
68, 07.2014, 333333, 1020
]
;
Left Join (Table1) LOAD * Resident Table2;
DROP Table Table2;

View solution in original post

3 Replies
nagaiank
Specialist III
Specialist III

Try the following script instead:

Table1:

LOAD * INLINE [
KEY, Year, Material, data1
68, 07.2014, 111111, 100
68, 07.2014, 222222, 101
68, 07.2014, 333333, 102
70, 07.2014, 444444, 500
]
;
Table2:
LOAD * inline [
KEY, Year, Material, data2
70, 07.2014, 444444, 5000
]
;
Concatenate
LOAD * inline [
KEY, Year, Material, data2
68, 07.2014, 111111, 1000
68, 07.2014, 333333, 1020
]
;
Left Join (Table1) LOAD * Resident Table2;
DROP Table Table2;

Not applicable
Author

yes perfect !

Thank you very much.

jerem1234
Specialist II
Specialist II

It's because when you join the second table, the field data2 exists from the first time you joined, so therefore the join uses that new field to join onto.

So you'll have this table after the first join:

KEY, Year, Material, data1,    data2

68, 07.2014, 111111, 100,        -

68, 07.2014, 222222, 101,        -

68, 07.2014, 333333, 102,        -

70, 07.2014, 444444, 500,        5000

but when you try the second join, it tries to match on the fields:

KEY, Year, Material, data2

68, 07.2014, 111111, 1000

68, 07.2014, 333333, 1020

but these records don't exist in the table, so it actually joins nothing.

Hope this helps!