Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I need to load many files in the application and i have used a loop to achieve it. i have taken an example with only 2 files(xls) to do the test. In each files i have 2 tables (Sheet1 and sheet2) which i wish to join using left join.
The issue that i am encountering is that line of 1 data is not present in the final result set (where e=20). see below:
Expected result:
a | b | c | d | e |
1 | 2 | 3 | 4 | 12 |
5 | 6 | 7 | 8 | - |
10 | 11 | 12 | 13 | 20 |
14 | 15 | 16 | 17 | - |
Below is my join:
FOR x =0 to 1
if $(x) =0 then
//
TRACe 1st;
//NoConcatenate
CORP:
LOAD *
FROM
(biff, embedded labels, table is [Sheet1$]);
left Join(CORP)
LOAD *
FROM
(biff, embedded labels, table is [Sheet2$]);
ELSE
TRACe 2nd;
Concatenate(CORP)
LOAD a,
b,
c,
d
FROM
(biff, embedded labels, table is [Sheet1$]);
left join(CORP)
LOAD
c,
d,
e
FROM
(biff, embedded labels, table is [Sheet2$]);
ENDIF
NEXT x
Attached you will the excel files which i used as tables.
Can anyone help please?
Hi Viju,
the 2nd join will fail because the target table already has those columns. One solution is to load all tables concatenated and do a final join at the end:
FOR x =0 to 1
if $(x) =0 then
//NoConcatenate
CORP:
LOAD *
FROM
(biff, embedded labels, table is [Sheet1$]);
TO_JOIN:
LOAD *
FROM
(biff, embedded labels, table is [Sheet2$]);
ELSE
Concatenate(CORP)
LOAD a,
b,
c,
d
FROM
(biff, embedded labels, table is [Sheet1$]);
Concatenate(TO_JOIN)
LOAD
c,
d,
e
FROM
(biff, embedded labels, table is [Sheet2$]);
ENDIF
NEXT x
left join(CORP)
LOAD * Resident TO_JOIN;
drop table TO_JOIN;
- Ralf