14 Replies Latest reply: Dec 23, 2016 7:35 AM by Eduardo DImperio RSS

    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


      Peek.JPG

        • Re: Problem with PEEK()
          Vineeth Pujari

          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;

          • Re: Problem with PEEK()
            Sunny Talwar

            Try this:

             

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

            • Re: Problem with PEEK()
              vijayakrishna parimi

              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

                • Re: Problem with PEEK()
                  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;

                • Re: Problem with PEEK()
                  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

                  • Re: Problem with PEEK()
                    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;

                     

                     

                    Peek.JPG

                    • Re: Problem with PEEK()
                      Robert Hutchings

                      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

                      • Re: Problem with PEEK()
                        Eduardo DImperio

                        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