topic Re: Problem with Left Join in QlikView

Problem with Left Join

Mon, 25 Aug 2014 13:52:05 GMT

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.

Re: Problem with Left Join

nagaiank — Mon, 25 Aug 2014 14:01:16 GMT

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;

Re: Problem with Left Join

Mon, 25 Aug 2014 14:07:06 GMT

yes perfect !

Thank you very much.

Re: Problem with Left Join

jerem1234 — Mon, 25 Aug 2014 14:09:38 GMT

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!