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.
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:
