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

[eduardo_dimperio]

I did another modification and didnt work too

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,

CONSUMO_REAL0

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

    RESIDENT TMP3

    ORDER BY OID_METER DESC;

    DROP TABLE TMP3;

[sunny_talwar]

Try this:

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), CONSUMODIA0 - PEEK(CONSUMODIA0)) AS CONSUMO_REAL0,

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

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

Resident TMP3

Where Len(Trim(CONSUMODIA0)) > 0

Order By OID_METER;

DROP TABLE TMP3;

[robert99]

I tried this once when I couldn't get Peek to work (or maybe it was in Power BI)

Add rowno() to the main table when ordering

Create a new table from this table loading RowNo() + 1 plus the required field (renamed) with a left join (by RowNo()

then do the calculation and see if the problem still exists

[william_allen]

Does Peek() not need single quotes around the field, like Peek('OID_METER')

[eduardo_dimperio · Accepted solution]

Hi everyone,

I solve the problem with this code below:

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()) AND VALUE_READ-PEEK(VALUE_READ)>0,VALUE_READ-PEEK(VALUE_READ))  as CONSUMODIA0,

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

    If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-2)AND VALUE_READ-PEEK(VALUE_READ)>0,VALUE_READ-PEEK(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,DATE_READ;

DROP TABLE TMP,TMP2;

NoConcatenate

TMP4:

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,

CONSUMODIA0,

CONSUMODIA1,

CONSUMODIA2

RESIDENT Exclusao

WHERE HOUR(DATE_READ)=5;

DROP TABLE Exclusao;

I still have one problem, but is not related with Peek(), so i'll open another ticket.

Thanks all