Joins not working in loop

[#1]

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?

[rbecher]

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