Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
eduardo_dimperio
Specialist II
Specialist II

Peek() duplicating values

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?Duplicated.JPG

1 Solution

Accepted Solutions
eduardo_dimperio
Specialist II
Specialist II
Author

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

View solution in original post

8 Replies
swuehl
MVP
MVP

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
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

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:

Previous.JPG

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

swuehl
MVP
MVP

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
Specialist II
Specialist II
Author

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
MVP
MVP

Try adding a counter using

LOAD

     RowNo() as RowID,

     ...

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

eduardo_dimperio
Specialist II
Specialist II
Author

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:

Peek.JPG

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
Specialist II
Specialist II
Author

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