Problem with PEEK()

[eduardo_dimperio]

Hi everyone,

Once again i need some help and thank's for all help that community are give to me

Well, i dont understand why my code using  Peek() is returning NULL.

TMP3 is running ok

TMP3:

LOAD

DISTINCT

    OID_METER,

    DATE_READ,

    ID_LEITURA,

    NAME_SYSTEM,

    NAME_GROUP,

    NEIGHB_SYSTEM,

    CITY_SYSTEM,

    NAME_TYPE_METER,

    TYPE_CONSTANT,

    NAME_UC,

    ADDRESS_BLOCK_UC,

    VALUE_READ,

    If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()),VALUE_READ)  as   CONSUMODIA0,

    If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-1),VALUE_READ)  as CONSUMODIA1,

    If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-2),VALUE_READ)  as CONSUMODIA2

    RESIDENT TMP2

    WHERE (HOUR(DATE_READ)=2 OR HOUR(DATE_READ)=5) AND MINUTE(DATE_READ)=0

    ORDER BY OID_METER;

DROP TABLE TMP,TMP2;

ANALISE_TMP:

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,

    CONSUMODIA0,

    CONSUMODIA1,

    CONSUMODIA2,

    If (OID_METER =PEEK(OID_METER), PEEK(CONSUMODIA0)- CONSUMODIA0) AS CONSUMO_REAL0,

    If (OID_METER =PEEK(OID_METER), PEEK(CONSUMODIA1)- CONSUMODIA1) AS CONSUMO_REAL1,

    If (OID_METER =PEEK(OID_METER), PEEK(CONSUMODIA2)- CONSUMODIA2) AS CONSUMO_REAL2

    RESIDENT TMP3

    ORDER BY OID_METER DESC;

     DROP TABLE TMP3;

I don't understant why PEEK(CONSUMODIA0)- CONSUMODIA0 return null if i have value on this fields

[vinieme12]

you condition for evaluation could be failing that's why

TRY as below with Previous

ANALISE_TMP:

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,

    CONSUMODIA0,

    CONSUMODIA1,

    CONSUMODIA2,

    If (OID_METER =PREVIOUS(OID_METER), PEEK(CONSUMODIA0)- CONSUMODIA0) AS CONSUMO_REAL0,

    If (OID_METER =PREVIOUS(OID_METER), PEEK(CONSUMODIA1)- CONSUMODIA1) AS CONSUMO_REAL1,

    If (OID_METER =PREVIOUS(OID_METER), PEEK(CONSUMODIA2)- CONSUMODIA2) AS CONSUMO_REAL2

    RESIDENT TMP3

    ORDER BY OID_METER DESC;

     DROP TABLE TMP3;

[sunny_talwar]

Try this:

If(OID_METER = Peek(OID_METER), RangeSum(Peek(CONSUMODIA0), -CONSUMODIA0) as CONSUMO_REAL0,

[parimikittu]

Hi,

The output for the code "Peek(OID_METER)" is null which is stored in CONSUMODIA0 column of TMP3. That is being used in the "ANALISE_TMP".

Peek() function has two uses

1)Peek(columnname): Returns previous row value of the column. if no previous row value i.e incase of the first record where there is no record before the 1st record, it will return null.We need to handle it.

Example below

_MASTER_KEY:

load * Inline [

monther

1

2

3

4

5

];

MASTER_KEY:

load monther,

if(isnull(peek(monther)),0,(peek(monther))) as previous_value

Resident _MASTER_KEY;

OUTPUT is below

monther , previous_value

1,          0

2,          1

3,          2

4,          3

5,          4

2)Peek('Columnname',recordnum,'Tablename') :

in this case it will get the desired record value(given from recordnum) of the columnname given through "Columnname" f the given table given from "Tablename".Which should be previously loaded table.Check belowexample.

Peek('monther',3,'_MASTER_KEY');

returns 4

important things to note 1) Peek uses 0 as first record

2) single quotes are required to name a column and table.

In your case, u are using the first peek() so, please handle the null as stated in the example1

[eduardo_dimperio]

Hi Synny, doesnt work

[eduardo_dimperio]

Vineeth, doesnt work bro.

[sunny_talwar]

When you say doesn't work, does it mean that you still see null or are you running into other issues?

[eduardo_dimperio]

I try this, but not work

ANALISE_TMP:

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,

    CONSUMODIA0,

    CONSUMODIA1,

    CONSUMODIA2,

    If(OID_METER=  Peek(OID_METER) AND Isnull(Peek(CONSUMODIA0)=0 ),(Peek(CONSUMODIA0)- CONSUMODIA0))  as CONSUMO_REAL0

//  If (OID_METER =PEEK(OID_METER), PEEK(CONSUMODIA1)- CONSUMODIA1) AS CONSUMO_REAL1,

//    If (OID_METER =PEEK(OID_METER), PEEK(CONSUMODIA2)- CONSUMODIA2) AS CONSUMO_REAL2

    RESIDENT TMP3

    ORDER BY OID_METER DESC;

[eduardo_dimperio]

I tried filter by Date, because CONSUMODIA0 only have record when date=today(). So i have value 22/12 5:00 and 22/12 2:00.

ANALISE_TMP:

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,

    CONSUMODIA0,

    CONSUMODIA1,

    CONSUMODIA2,

     If(OID_METER=  Peek(OID_METER) AND DATE_READ = TODAY(),Peek(CONSUMODIA0)AS CONSUMO_REAL0

    RESIDENT TMP3 

    ORDER BY OID_METER DESC;

and didnt work too

[eduardo_dimperio]

Other issues, get wrong numbers output:

The right output need to be a single row with 20 as value (177959 - 177939)