topic Left join inside loop in QlikView

Left join inside loop

Wed, 21 Mar 2012 14:05:26 GMT

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);

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?

Left join inside loop

Wed, 21 Mar 2012 14:08:41 GMT

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

Left join inside loop

CELAMBARASAN — Wed, 21 Mar 2012 14:34:59 GMT

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;

Celambarasan

Left join inside loop

Anonymous — Wed, 21 Mar 2012 14:37:39 GMT

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.

Left join inside loop

Wed, 21 Mar 2012 14:55:22 GMT

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

Left join inside loop

Wed, 21 Mar 2012 14:56:38 GMT

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

Left join inside loop

Anonymous — Wed, 21 Mar 2012 15:03:47 GMT

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

(Or was it response to Celambarasan?)

Left join inside loop

Wed, 21 Mar 2012 15:34:17 GMT

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);

Left join inside loop

Anonymous — Wed, 21 Mar 2012 15:48:51 GMT

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);

Left join inside loop

Wed, 21 Mar 2012 15:53:58 GMT

Thanks