Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Left join inside loop

Hi.

I'm having trouble with the following script:

LET StartYear = 2011;

LET StartMonth = 1;

FOR a=StartYear to 2011

FOR m= StartMonth to 3

OrcTemp:

Load

    Ano,

    CDUNIDADEORCAMENTARIA,

    CDSUBACAO,

    CDFONTERECURSO,

    CodigoEspecificacao,

    ValorInicial,

    $(m) as Mes

Resident

    Dotacao

Where

    Ano=$(a);

Left Join

LOAD

    Ano,

    CDUNIDADEORCAMENTARIA,

    CDSUBACAO,

    CDFONTERECURSO,

    CodigoEspecificacao,

    ValorAlterado

Resident

    Movimentacao

Where

    Month(Data) <= $(m);

next

next

Qlikview is creating a different table with the suffix "-N" for each iteration. When I try remove the left join, everything works fine. Any ideas about what am I doing wrong?

1 Solution

Accepted Solutions
mov
Not applicable

Left join inside loop

Not what I meant.  This is the correct one (notice bold):

LET StartYear = 2011;

LET MonthYear = 1;


OrcTemp:

Load

    2009 as Ano,

    1 as CDUNIDADEORCAMENTARIA,

    1 as CDSUBACAO,

    1 as CDFONTERECURSO,

    1 as CodigoEspecificacao,

    1 as ValorInicial,

    1 as Mes

AutoGenerate 1;


FOR a=AnoInicio to 2011


FOR m= If(a=AnoInicio,MesInicio,1) to 3


CONCATENATE (OrcTemp)

Load

    Ano,

    CDUNIDADEORCAMENTARIA,

    CDSUBACAO,

    CDFONTERECURSO,

    CodigoEspecificacao,

    ValorInicial,

    $(m) as Mes

Resident

    Dotacao

Where

    Ano=$(a);

   

Left Join (OrcTemp)

LOAD

    Ano,

    CDUNIDADEORCAMENTARIA,

    CDSUBACAO,

    CDFONTERECURSO,

    CodigoEspecificacao,

    ValorAlterado

Resident

    Movimentacao

Where

    Month(Data) <= $(m);


next


next

9 Replies
Not applicable

Left join inside loop

p.s. The expected result was all rows into only one table(OrcTemp).

celambarasan
Not applicable

Left join inside loop

Hi,

     Try with this

    

LET StartYear = 2011;

LET StartMonth = 1;

FOR a=StartYear to 2011

FOR m= StartMonth to 3

OrcTemp:

Load

    Ano,

    CDUNIDADEORCAMENTARIA,

    CDSUBACAO,

    CDFONTERECURSO,

    CodigoEspecificacao,

    ValorInicial,

    $(m) as Mes

Resident

    Dotacao

Where

    Ano=$(a);

NoConcatenate

Orc:

Load

          *

Resident OrcTemp;

Left Join

LOAD

    Ano,

    CDUNIDADEORCAMENTARIA,

    CDSUBACAO,

    CDFONTERECURSO,

    CodigoEspecificacao,

    ValorAlterado

Resident

    Movimentacao

Where

    Month(Data) <= $(m);

Drop Table OrcTemp;

next

next

Celambarasan

mov
Not applicable

Left join inside loop

Try this:
Create blank table OrcTemp (without any records) before loops.  You can use load inline or load autogenerate 1.
In the loop, where you now have table name, replace it with CONCATENATE (OrcTemp);
In the join, add table name: LEFT JOIN (OrcTemp).

See if it helps.

Not applicable

Left join inside loop

Doesnt worked. The problem persisted. When the Left Join is executed a -N table is created the same way as the previous solution.

Not applicable

Left join inside loop

Doesnt worked. The problem persisted. When the Left Join is executed a -N table is created the same way as the previous solution.

mov
Not applicable

Left join inside loop

This is strange.  Can I you show the script after the change?

(Or was it response to Celambarasan?)

Not applicable

Left join inside loop

LET StartYear = 2011;

LET MonthYear = 1;

OrcTemp:

Load

    2009 as Ano,

    1 as CDUNIDADEORCAMENTARIA,

    1 as CDSUBACAO,

    1 as CDFONTERECURSO,

    1 as CodigoEspecificacao,

    1 as ValorInicial,

    1 as Mes

AutoGenerate 1;

FOR a=AnoInicio to 2011

FOR m= If(a=AnoInicio,MesInicio,1) to 3

OrcTemp:

Load

    Ano,

    CDUNIDADEORCAMENTARIA,

    CDSUBACAO,

    CDFONTERECURSO,

    CodigoEspecificacao,

    ValorInicial,

    $(m) as Mes

Resident

    Dotacao

Where

    Ano=$(a);

   

Left Join

LOAD

    Ano,

    CDUNIDADEORCAMENTARIA,

    CDSUBACAO,

    CDFONTERECURSO,

    CodigoEspecificacao,

    ValorAlterado

Resident

    Movimentacao

Where

    Month(Data) <= $(m);

next

next

mov
Not applicable

Left join inside loop

Not what I meant.  This is the correct one (notice bold):

LET StartYear = 2011;

LET MonthYear = 1;


OrcTemp:

Load

    2009 as Ano,

    1 as CDUNIDADEORCAMENTARIA,

    1 as CDSUBACAO,

    1 as CDFONTERECURSO,

    1 as CodigoEspecificacao,

    1 as ValorInicial,

    1 as Mes

AutoGenerate 1;


FOR a=AnoInicio to 2011


FOR m= If(a=AnoInicio,MesInicio,1) to 3


CONCATENATE (OrcTemp)

Load

    Ano,

    CDUNIDADEORCAMENTARIA,

    CDSUBACAO,

    CDFONTERECURSO,

    CodigoEspecificacao,

    ValorInicial,

    $(m) as Mes

Resident

    Dotacao

Where

    Ano=$(a);

   

Left Join (OrcTemp)

LOAD

    Ano,

    CDUNIDADEORCAMENTARIA,

    CDSUBACAO,

    CDFONTERECURSO,

    CodigoEspecificacao,

    ValorAlterado

Resident

    Movimentacao

Where

    Month(Data) <= $(m);


next


next

Not applicable

Left join inside loop

Thanks