Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Joins not working in loop

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:

abcde
123412
5678-
1011121320
14151617-

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?

1 Reply
MVP
MVP

Re: Joins not working in loop

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

Community Browser