Peek() duplicating values

[eduardo_dimperio]

Hi people,

Last question before Xmas

I have some meters and correspondent values,if the previous value is equal to current, ill ignore this meter.

So i did this:

Exclude_Meters:

LOAD

  DISTINCT OID_METER,

    VALUE_READ,

    If(OID_METER=  Peek(OID_METER) and(Peek(VALUE_READ)- VALUE_READ)=0,OID_METER)  as EXCLUDE_METER

    RESIDENT TMP2

    ORDER BY OID_METER;

But when i check Peek(VALUE_READ) and VALUE_READ discover duplicated values in Peek(VALUE_READ) , for that reason sometimes i got wrong answers. How can i remove it?

[swuehl]

Not sure I understood your issue. What is ANTERIOR? It's not part of your table.

Are you showing fields from different tables in your chart?

Also note that ORDER BY is sorting the input table records, not the output table records.

And DISTINCT keyword might mix your output table records in an unexpected way, not sure if this interferes with your use of PEEK(). May be better to use PREVIOUS() instead.

[eduardo_dimperio]

Hi Stefan, i already saw some posts of you helping people and thanks for that.

Well, ANTERIOR (means previous in my language), is Peek(VALUE_READ) that i put in a field to show the output.

What i need to do is Peek(VALUE_READ)- VALUE_READ) and if that value is zero i discard it, but how you can see that value are duplicated. So when i do Peek(VALUE_READ)- VALUE_READ) in the first line i expect from result: 43923 - 44083 and not 43923-43923.

[eduardo_dimperio]

I did this:

OID_METER,

VALUE_READ,

Previous(VALUE_READ) AS ANTERIOR,

If(OID_METER=  Peek(OID_METER),(Previous(VALUE_READ)- VALUE_READ)) AS Resultado,

DATE_READ

RESIDENT TMP2

where DATE_READ>TODAY()-5 AND OID_METER=74879

ORDER BY OID_METER,DATE_READ;

And get this result:

Why i got this randon values? I expect previous(value_read)=51309 for value read=53777 and not 52191

[swuehl]

Could you post some sample input records?

It's hard to tell what is exactely going on without knowing the data. Looking at a table chart might be misleading, since the sort order of that table is most probably different from the order of input records in the script.

Not sure if you familiar with the concept of the input and output table records, and how you can address these by using Previous() or Peek(). You are mixing Peek() and Previous() in your script, this might be one piece of the issue cake.

[eduardo_dimperio]

Hi Stefan,

Im not understand what you mean by input tables records, you want to see the whole code?

Reading your answer i really believe the order is the key to get right data.

See here my original code:

TMP:

LOAD

    OID_SYSTEM,

    OID_GROUP,

    NAME_SYSTEM,

    NEIGHB_SYSTEM,

    CITY_SYSTEM,

  SSN_REMOTE_SYSTEM,

    STATUS,

    ID_SYSTEM_TYPE

FROM [lib://MI_csv/RS_SYSTEM.qvd]

(qvd) WHERE STATUS<>'99' and OID_GROUP<>'28';

INNER JOIN (TMP)

LOAD

    OID_GROUP,

    NAME_GROUP

FROM [lib://MI_csv/RS_GROUP.qvd]

(qvd);

INNER JOIN (TMP)

LOAD

    OID_UC,

    NAME_UC,

    ADDRESS_BLOCK_UC,

    OID_SYSTEM

FROM [lib://MI_csv/RS_UC.qvd]

(qvd) WHERE NAME_UC<>'Piscina Coberta' and NAME_UC<>'Piscina Infantil Adulto' and NAME_UC<>'MEDIDOR PARALELO' and NAME_UC<>'MEDIDOR PISCINA';

INNER JOIN (TMP)

LOAD

    OID_METER,

    OID_UC,

    OID_TYPE_METER,

    TYPE_CONSTANT

FROM [lib://MI_csv/RS_METER.qvd]

(qvd);

INNER JOIN (TMP)

LOAD

    OID_TYPE_METER,

    NAME_TYPE_METER

FROM [lib://MI_csv/RS_TYPE_METER.qvd]

(qvd);

INNER JOIN (TMP)

LOAD

    OID_COUNTERS,

    OID_METER,

    CONCENTRATOR_COUNTERS,

    PORT_COUNTERS

FROM [lib://MI_csv/RS_COUNTERS.qvd]

(qvd);

TMP2:

LOAD

NAME_SYSTEM,

NAME_GROUP,

NEIGHB_SYSTEM,

CITY_SYSTEM,

OID_METER,

NAME_TYPE_METER,

TYPE_CONSTANT,

OID_UC,

NAME_UC,

ADDRESS_BLOCK_UC,

OID_SYSTEM&'|'&CONCENTRATOR_COUNTERS&'|'&PORT_COUNTERS AS ID_LEITURA

RESIDENT TMP;

left join (TMP2)

LOAD

    DATE_READ,

    VALUE_READ,

    ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA

RESIDENT CONCAT_TABLE where DATE_READ>TODAY()-5;

DROP TABLE CONCAT_TABLE;

DROP TABLE TMP;

TestaValor:

LOAD

OID_METER,

VALUE_READ,

PEEK(VALUE_READ) AS ANTERIOR,

If(OID_METER=  Peek(OID_METER),(PEEK(VALUE_READ)- VALUE_READ)) AS Resultado,

DATE_READ

TIMESTAMP(DATE_READ,'DD/MM hh:mm:ss')

RESIDENT TMP2

where DATE_READ>TODAY()-5 AND OID_METER=74879

ORDER BY OID_METER,DATE_READ;

drop table TMP2;

Is that you need?

From output i just need to get Value_read - Peek(Value_read)

[swuehl]

Try adding a counter using

LOAD

     RowNo() as RowID,

     ...

and add RowID to your chart as dimension. Sort by RowID.

[eduardo_dimperio]

Hey Stefan,

i Tried and didnt worked, but i figure out how to solve, but now i have other problem.

This is my new code.

Corrige_Medidor:

LOAD

DISTINCT

OID_METER,

VALUE_READ AS VALOR,

DATE_READ

RESIDENT TMP2

ORDER BY OID_METER,DATE_READ DESC ;

NoConcatenate

Exclude_Meter:

LOAD

DISTINCT

OID_METER,

VALOR,

PEEK(VALOR),

IF(VALOR - PEEK(VALOR)=0,OID_METER) AS EXCLUIR,

DATE_READ

RESIDENT Corrige_Medidor

ORDER BY OID_METER,DATE_READ DESC ;

drop table Corrige_Medidor;

Exclusao:

LOAD

  OID_METER,

    EXCLUIR

    RESIDENT Exclude_Meter

    WHERE Isnull(EXCLUIR)=0

    ORDER BY OID_METER;

And i get this result:

This is right, but now i need to pick just the values on OID_METER that doent have correspondent on EXCLUIR.

I tried this:

Exclusao2:

LOAD

  OID_METER,

    EXCLUIR

    RESIDENT Exclude_Meter

    WHERE Isnull(EXCLUIR)=-1

    ORDER BY OID_METER;

and this:

Exclusao2:

LOAD

  OID_METER,

    EXCLUIR

    RESIDENT Exclude_Meter

    WHERE  EXCLUIR <> OID_METER

    ORDER BY OID_METER;

even this:

Exclusao2:

LOAD

  OID_METER,

    EXCLUIR,

If (not exists (OID_METER,EXCLUIR)=-1, OID_METER)

    RESIDENT Exclude_Meter

    ORDER BY OID_METER;

but nothing worked. My OID_METER returns no record.

[eduardo_dimperio · Accepted solution]

Hi everyone,

I finally get the right output, follow the code:

Corrige_Medidor:

LOAD

DISTINCT

OID_METER,

VALUE_READ AS VALOR,

DATE_READ

RESIDENT TMP2

ORDER BY OID_METER,DATE_READ DESC ;

NoConcatenate

Exclude_Meter:

LOAD

DISTINCT

OID_METER,

VALOR,

PEEK(VALOR),

IF(VALOR - PEEK(VALOR)=0,OID_METER) AS EXCLUIR,

DATE_READ

RESIDENT Corrige_Medidor

ORDER BY OID_METER,DATE_READ DESC ;

drop table Corrige_Medidor;

Exclusao:

LOAD

    OID_METER,

    EXCLUIR

    RESIDENT Exclude_Meter

    WHERE NOT EXISTS(EXCLUIR,OID_METER)=-1

    ORDER BY EXCLUIR;

DROP TABLE Exclude_Meter;

Thank you all