Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
yes perfect !
Thank you very much.
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!