Help with a code

[eduardo_dimperio]

Hi guys, I was looking a old code (that works very well) and made some changes and get strange results. For that i need some help to understand 2 points:

1-  This code returns 736 lines, but if i change val0,val1 and val2 for value_read (note that not change anything), its pass to return 0 lines

2- i dont need to use preceding load to use some variable that was created and used at the same load, like Peek(val0), Peek(val1) and Peek(val2).

What im missing?

ANALISE_TMP:

LOAD

  ID_LEITURA,

    NAME_SYSTEM,

    NAME_GROUP,

    NEIGHB_SYSTEM,

    CITY_SYSTEM,

    OID_METER,

    NAME_TYPE_METER,

    TYPE_CONSTANT,

    NAME_UC,

    ADDRESS_BLOCK_UC,

    DATE_READ AS DIA0,

    VALUE_READ AS Val0,

    (IF(OID_METER=PEEK(OID_METER),PEEK(Val0)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA0

  RESIDENT TMP2

    where FLOOR(DATE_READ)=FLOOR(TODAY())

    ORDER BY OID_METER, DATE_READ DESC;

LEFT JOIN (ANALISE_TMP)

LOAD

  ID_LEITURA,

    OID_METER,

    TYPE_CONSTANT,

    DATE_READ AS DIA1,

    VALUE_READ AS Val1,

    (IF(OID_METER=PEEK(OID_METER),PEEK(Val1)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA1

  RESIDENT TMP2

    where FLOOR(DATE_READ)=FLOOR(TODAY()-1)

    ORDER BY OID_METER, DATE_READ DESC;

LEFT JOIN (ANALISE_TMP)

LOAD

  ID_LEITURA,

    OID_METER,

    TYPE_CONSTANT,

    DATE_READ AS DIA2,

    VALUE_READ AS Val2,

    (IF(OID_METER=PEEK(OID_METER),PEEK(Val2)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA2

  RESIDENT TMP2

    where FLOOR(DATE_READ)=FLOOR(TODAY()-2)

    ORDER BY OID_METER, DATE_READ DESC;

DROP TABLE TMP2;

[sunny_talwar]

I think peek works on your current table and previous works on your table from which you are pulling the data (resident table) in your case. Since VALUE_READ is renamed to Val1, Val2, & Val0, you need to use these new fields when you use Peek. May be try using Previous(VALUE_READ) and see what you get?

[flipside]

If I understand correctly, you previously had Val0, Val1 and Val2 as separate fields, but now you only have one field (value_read) which becomes one of the joining fields in the two left joins so the logic is different.

flipside

[eduardo_dimperio]

Yeah i agree, but the diference occur when i delete Val1, Val2  and Val0. So the current table has only VALUE_READ, is like Val1, Val2  Val0 never exist and thats strange.

[sunny_talwar]

Peek function is able to read fields which are getting created in the current load. So although the Val0 just got created, peek function is able to still use it to do stuff.

Same way we use Peek() function outside of a table load to pull value into a variable. I don't know if you have seen master calendar script or not, but max and min dates are saved into variables using peek() function.

[eduardo_dimperio]

Hi Flipside, no no.

I will post the both code to better understanding

Original Code:

ANALISE_TMP:

LOAD

  ID_LEITURA,

    NAME_SYSTEM,

    NAME_GROUP,

    NEIGHB_SYSTEM,

    CITY_SYSTEM,

    OID_METER,

    NAME_TYPE_METER,

    TYPE_CONSTANT,

    NAME_UC,

    ADDRESS_BLOCK_UC,

    DATE_READ AS DIA0,

    VALUE_READ AS Val0,

    (IF(OID_METER=PEEK(OID_METER),PEEK(Val0)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA0

  RESIDENT TMP2

    where FLOOR(DATE_READ)=FLOOR(TODAY())

    ORDER BY OID_METER, DATE_READ DESC;

LEFT JOIN (ANALISE_TMP)

LOAD

  ID_LEITURA,

    OID_METER,

    TYPE_CONSTANT,

    DATE_READ AS DIA1,

   VALUE_READ AS Val1,

    (IF(OID_METER=PEEK(OID_METER),PEEK(Val1)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA1

  RESIDENT TMP2

    where FLOOR(DATE_READ)=FLOOR(TODAY()-1)

    ORDER BY OID_METER, DATE_READ DESC;

LEFT JOIN (ANALISE_TMP)

LOAD

  ID_LEITURA,

    OID_METER,

    TYPE_CONSTANT,

    DATE_READ AS DIA2,

   VALUE_READ AS Val2,

    (IF(OID_METER=PEEK(OID_METER),PEEK(Val2)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA2

  RESIDENT TMP2

    where FLOOR(DATE_READ)=FLOOR(TODAY()-2)

    ORDER BY OID_METER, DATE_READ DESC;

Changed Code:

ANALISE_TMP:

LOAD

  ID_LEITURA,

    NAME_SYSTEM,

    NAME_GROUP,

    NEIGHB_SYSTEM,

    CITY_SYSTEM,

    OID_METER,

    NAME_TYPE_METER,

    TYPE_CONSTANT,

    NAME_UC,

    ADDRESS_BLOCK_UC,

    DATE_READ AS DIA0,

    VALUE_READ,

    (IF(OID_METER=PEEK(OID_METER),PEEK(VALUE_READ)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA0

  RESIDENT TMP2

    where FLOOR(DATE_READ)=FLOOR(TODAY())

    ORDER BY OID_METER, DATE_READ DESC;

LEFT JOIN (ANALISE_TMP)

LOAD

  ID_LEITURA,

    OID_METER,

    TYPE_CONSTANT,

    DATE_READ AS DIA1,

    VALUE_READ,

    (IF(OID_METER=PEEK(OID_METER),PEEK(VALUE_READ)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA1

  RESIDENT TMP2

    where FLOOR(DATE_READ)=FLOOR(TODAY()-1)

    ORDER BY OID_METER, DATE_READ DESC;

LEFT JOIN (ANALISE_TMP)

LOAD

  ID_LEITURA,

    OID_METER,

    TYPE_CONSTANT,

    DATE_READ AS DIA2,

    VALUE_READ,

    (IF(OID_METER=PEEK(OID_METER),PEEK(VALUE_READ)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA2

  RESIDENT TMP2

    where FLOOR(DATE_READ)=FLOOR(TODAY()-2)

    ORDER BY OID_METER, DATE_READ DESC;

Its the same code, if Val0 = Value_read, why when i replace the values change?

[eduardo_dimperio]

Hum, very interesting. Thanks for that, but i still dont get the diference between Peek(VALUE_READ) and PEEK(Val0)

if VALUE_READ AS VAL0 i thought the Value_read = Val0. Right?

[flipside · Accepted solution]

Hi Eduardo,

You may have already resolved this, but the reason is in your first code you create a table with fields ..

ID_LEITURA,

NAME_SYSTEM,

NAME_GROUP,

NEIGHB_SYSTEM,

CITY_SYSTEM,

OID_METER,

NAME_TYPE_METER,

TYPE_CONSTANT,

NAME_UC,

ADDRESS_BLOCK_UC,

DIA0,

Val0,

CONSUMODIA0

.. and then join these fields to it ...

ID_LEITURA,

OID_METER,

TYPE_CONSTANT,

DIA1,

Val1,

CONSUMODIA1

... the 3x common fields are ID_LEITURA, OID_METER & TYPE_CONSTANT and this is how it joins the tables, on a combination of these fields.

In the second code you are creating a table with fields ...

ID_LEITURA,

NAME_SYSTEM,

NAME_GROUP,

NEIGHB_SYSTEM,

CITY_SYSTEM,

OID_METER,

NAME_TYPE_METER,

TYPE_CONSTANT,

NAME_UC,

ADDRESS_BLOCK_UC,

DIA0,

VALUE_READ,

CONSUMODIA0

.. and then join these fields to it ...

ID_LEITURA,

OID_METER,

TYPE_CONSTANT,

DIA1,

VALUE_READ,

CONSUMODIA1

.. there are now FOUR common fields ID_LEITURA, OID_METER, TYPE_CONSTANT & VALUE_READ. You will only return data if VALUE_READ for DIA0 is the same as DIA1 which I guess isn't what you intended.

flipside

[eduardo_dimperio]

Hi Flipside,

I think you're totally right about that.

Finally i understand.

Thank you