Variable don't recognized inside a IF

[eduardo_dimperio]

Hi,

Im new in qlik sense and i have a little problem with this code below:

Set dia=0;

Do while dia<=2   

left join (ANALISE_TMP)

LOAD

  OID_METER,

    VALUE_READ*TYPE_CONSTANT AS CONSUMODIA$(dia),

    PEEK(VALUE_READ)*TYPE_CONSTANT AS CONSUMO_ANTERIOR_DIA$(dia),

    IF (OID_METER=PEEK(OID_METER),

        CONSUMODIA$(dia)-CONSUMO_ANTERIOR_DIA$(dia)) AS CONSUMO_REAL$(dia)

RESIDENT TMP2

where FLOOR(DATE_READ)=FLOOR(TODAY()-$(dia))

ORDER BY OID_METER,DATE_READ DESC;

Let dia=dia+1;

LooP

For some reason qlik sense understant my loop, create the variable CONSUMODIA0, but give me error :

Field not found - <CONSUMODIA0>

O erro ocorreu aqui:

left join (ANALISE_TMP) LOAD OID_METER, VALUE_READ*TYPE_CONSTANT AS CONSUMODIA0, PEEK(VALUE_READ)*TYPE_CONSTANT AS CONSUMO_ANTERIOR_DIA0, IF (OID_METER=PEEK(OID_METER), CONSUMODIA0-CONSUMO_ANTERIOR_DIA0) AS CONSUMO_REAL0 RESIDENT TMP2 where FLOOR(DATE_READ)=FLOOR(TODAY()-0) ORDER BY OID_METER,DATE_READ DESC

Some help please

[sunny_talwar · Accepted solution]

You are using this If statement

IF (OID_METER=PEEK(OID_METER),

        CONSUMODIA$(dia)-CONSUMO_ANTERIOR_DIA$(dia)) AS CONSUMO_REAL$(dia)

which is accessing CONSUMODIA$(dia) which is also getting created in the same load. VALUE_READ*TYPE_CONSTANT AS CONSUMODIA$(dia). May be try this:

Set dia=0;

Do while dia<=2  

Left Join (ANALISE_TMP)

LOAD *,

     If(OID_METER=PEEK(OID_METER), CONSUMODIA$(dia)-CONSUMO_ANTERIOR_DIA$(dia)) as CONSUMO_REAL$(dia);

LOAD OID_METER,

    VALUE_READ*TYPE_CONSTANT as CONSUMODIA$(dia),

    PEEK(VALUE_READ)*TYPE_CONSTANT as CONSUMO_ANTERIOR_DIA$(dia)

Resident TMP2

Where FLOOR(DATE_READ)=FLOOR(TODAY()-$(dia))

Order By OID_METER,DATE_READ desc;

LET dia=dia+1;

LOOP

[Gysbert_Wassenaar]

You can't create a new field in one load statement and also in that same load statement use it as if it already exists in the source table. You can only use it when it's already created. For example in a preceding load or when using the peek function.

[eduardo_dimperio]

Hi Sunny, thanks again to be so fast.

I didnt understant your code.

First you use inside the if:

  If(OID_METER=PEEK(OID_METER), CONSUMODIA$(dia)-CONSUMO_ANTERIOR_DIA$(dia)) as CONSUMO_REAL$(dia);

to after that you create the variable?

LOAD OID_METER,

    VALUE_READ*TYPE_CONSTANT as CONSUMODIA$(dia)

another question, can i use 2 loads without a join?

[eduardo_dimperio]

Is this a Precending Load?

[sunny_talwar]

This is a preceding load....

[sunny_talwar]

I think you got the answer, the reason I can do this is because it is preceding load which goes from bottom up

[eduardo_dimperio]

Hi ,

Thank you so much, i understand this concept, but dont know why this code are more slow that without a loop.

I try to change but now my "If" dont compile.

Dont know if i have to open a new question or put here because is the same code.

Do while dia<=2

ANALISE_TMP$(dia):

LOAD

  OID_METER,

  ID_LEITURA,

    NAME_SYSTEM,

    NAME_GROUP,

    NEIGHB_SYSTEM,

    CITY_SYSTEM,

    NAME_TYPE_METER,

    TYPE_CONSTANT,

    NAME_UC,

    ADDRESS_BLOCK_UC,

    DATE_READ,

If(OID_METER=PEEK(OID_METER), VALUE_READ-PEEK(VALUE_READ)) as CONSUMO_REAL$(dia)

Resident TMP2

Where FLOOR(DATE_READ)=FLOOR(TODAY()-$(dia))

Order By OID_METER,DATE_READ desc;

if(dia<>0 , LEFT JOIN (ANALISE_TMP$(dia));

dia=dia+1;

Loop

DROP TABLES TMP, TMP2;