Left join inside loop

[#1]

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?

[#2]

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

[CELAMBARASAN]

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

[Anonymous]

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.

[#5]

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

[#6]

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

[Anonymous]

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

(Or was it response to Celambarasan?)

[#8]

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

[Anonymous · Accepted solution]

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

[#10]

Thanks