Joins not working in loop

Mon, 23 Sep 2013 11:41:44 GMT

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,

FROM

(biff, embedded labels, table is [Sheet1$]);

left join(CORP)

LOAD

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?

Re: Joins not working in loop

rbecher — Mon, 23 Sep 2013 20:33:12 GMT

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

topic Re: Joins not working in loop in QlikView

Joins not working in loop

Re: Joins not working in loop