Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
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
Valued Contributor III

Re: Problem with Left Join

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;

3 Replies
nagaiank
Valued Contributor III

Re: Problem with Left Join

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

Re: Problem with Left Join

yes perfect !

Thank you very much.

jerem1234
Valued Contributor II

Re: Problem with Left Join

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!