8 Replies Latest reply: Dec 29, 2016 7:11 AM by Eduardo DImperio RSS

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

        • Re: Peek() duplicating values
          Stefan Wühl

          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.

            • Re: Peek() duplicating values
              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.

                • Re: Peek() duplicating values
                  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:

                   

                  Previous.JPG

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

                    • Re: Peek() duplicating values
                      Stefan Wühl

                      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.

                        • Re: Peek() duplicating values
                          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)

                            • Re: Peek() duplicating values
                              Stefan Wühl

                              Try adding a counter using

                               

                              LOAD

                                   RowNo() as RowID,

                                   ...

                               

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

                                • Re: Peek() duplicating values
                                  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:

                                   

                                  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.

                      • Re: Peek() duplicating values
                        Eduardo DImperio

                        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