8 Replies Latest reply: Jan 6, 2017 2:03 PM by Eduardo DImperio RSS

    Help with a code

    Eduardo DImperio

      Hi guys, I was looking a old code (that works very well) and made some changes and get strange results. For that i need some help to understand 2 points:

       

      1-  This code returns 736 lines, but if i change val0,val1 and val2 for value_read (note that not change anything), its pass to return 0 lines

      2- i dont need to use preceding load to use some variable that was created and used at the same load, like Peek(val0), Peek(val1) and Peek(val2).

       

      What im missing?

       

       

      ANALISE_TMP:

      LOAD

        ID_LEITURA,

          NAME_SYSTEM,

          NAME_GROUP,

          NEIGHB_SYSTEM,

          CITY_SYSTEM,

          OID_METER,

          NAME_TYPE_METER,

          TYPE_CONSTANT,

          NAME_UC,

          ADDRESS_BLOCK_UC,

          DATE_READ AS DIA0,

          VALUE_READ AS Val0,

          (IF(OID_METER=PEEK(OID_METER),PEEK(Val0)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA0

        RESIDENT TMP2

          where FLOOR(DATE_READ)=FLOOR(TODAY())

          ORDER BY OID_METER, DATE_READ DESC;

       

       

      LEFT JOIN (ANALISE_TMP)

      LOAD

        ID_LEITURA,

          OID_METER,

          TYPE_CONSTANT,

          DATE_READ AS DIA1,

          VALUE_READ AS Val1,

          (IF(OID_METER=PEEK(OID_METER),PEEK(Val1)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA1

        RESIDENT TMP2

          where FLOOR(DATE_READ)=FLOOR(TODAY()-1)

          ORDER BY OID_METER, DATE_READ DESC;

       

       

      LEFT JOIN (ANALISE_TMP)

      LOAD

        ID_LEITURA,

          OID_METER,

          TYPE_CONSTANT,

          DATE_READ AS DIA2,

          VALUE_READ AS Val2,

          (IF(OID_METER=PEEK(OID_METER),PEEK(Val2)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA2

        RESIDENT TMP2

          where FLOOR(DATE_READ)=FLOOR(TODAY()-2)

          ORDER BY OID_METER, DATE_READ DESC;

         

      DROP TABLE TMP2;

        • Re: Help with a code
          Sunny Talwar

          I think peek works on your current table and previous works on your table from which you are pulling the data (resident table) in your case. Since VALUE_READ is renamed to Val1, Val2, & Val0, you need to use these new fields when you use Peek. May be try using Previous(VALUE_READ) and see what you get?

            • Re: Help with a code
              Eduardo DImperio

              Yeah i agree, but the diference occur when i delete Val1, Val2  and Val0. So the current table has only VALUE_READ, is like Val1, Val2  Val0 never exist and thats strange.

                • Re: Help with a code
                  Sunny Talwar

                  Peek function is able to read fields which are getting created in the current load. So although the Val0 just got created, peek function is able to still use it to do stuff.

                   

                  Same way we use Peek() function outside of a table load to pull value into a variable. I don't know if you have seen master calendar script or not, but max and min dates are saved into variables using peek() function.

              • Re: Help with a code
                Dave Riley

                If I understand correctly, you previously had Val0, Val1 and Val2 as separate fields, but now you only have one field (value_read) which becomes one of the joining fields in the two left joins so the logic is different.

                 

                flipside

                  • Re: Help with a code
                    Eduardo DImperio

                    Hi Flipside, no no.

                     

                    I will post the both code to better understanding

                     

                     

                    Original Code:

                     

                    ANALISE_TMP:

                    LOAD

                      ID_LEITURA,

                        NAME_SYSTEM,

                        NAME_GROUP,

                        NEIGHB_SYSTEM,

                        CITY_SYSTEM,

                        OID_METER,

                        NAME_TYPE_METER,

                        TYPE_CONSTANT,

                        NAME_UC,

                        ADDRESS_BLOCK_UC,

                        DATE_READ AS DIA0,

                        VALUE_READ AS Val0,

                        (IF(OID_METER=PEEK(OID_METER),PEEK(Val0)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA0

                      RESIDENT TMP2

                        where FLOOR(DATE_READ)=FLOOR(TODAY())

                        ORDER BY OID_METER, DATE_READ DESC;

                     

                     

                    LEFT JOIN (ANALISE_TMP)

                    LOAD

                      ID_LEITURA,

                        OID_METER,

                        TYPE_CONSTANT,

                        DATE_READ AS DIA1,

                       VALUE_READ AS Val1,

                        (IF(OID_METER=PEEK(OID_METER),PEEK(Val1)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA1

                      RESIDENT TMP2

                        where FLOOR(DATE_READ)=FLOOR(TODAY()-1)

                        ORDER BY OID_METER, DATE_READ DESC;

                     

                     

                    LEFT JOIN (ANALISE_TMP)

                    LOAD

                      ID_LEITURA,

                        OID_METER,

                        TYPE_CONSTANT,

                        DATE_READ AS DIA2,

                       VALUE_READ AS Val2,

                        (IF(OID_METER=PEEK(OID_METER),PEEK(Val2)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA2

                      RESIDENT TMP2

                        where FLOOR(DATE_READ)=FLOOR(TODAY()-2)

                        ORDER BY OID_METER, DATE_READ DESC;

                     

                     

                    Changed Code:

                    ANALISE_TMP:

                    LOAD

                      ID_LEITURA,

                        NAME_SYSTEM,

                        NAME_GROUP,

                        NEIGHB_SYSTEM,

                        CITY_SYSTEM,

                        OID_METER,

                        NAME_TYPE_METER,

                        TYPE_CONSTANT,

                        NAME_UC,

                        ADDRESS_BLOCK_UC,

                        DATE_READ AS DIA0,

                        VALUE_READ,

                        (IF(OID_METER=PEEK(OID_METER),PEEK(VALUE_READ)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA0

                      RESIDENT TMP2

                        where FLOOR(DATE_READ)=FLOOR(TODAY())

                        ORDER BY OID_METER, DATE_READ DESC;

                     

                     

                    LEFT JOIN (ANALISE_TMP)

                    LOAD

                      ID_LEITURA,

                        OID_METER,

                        TYPE_CONSTANT,

                        DATE_READ AS DIA1,

                        VALUE_READ,

                        (IF(OID_METER=PEEK(OID_METER),PEEK(VALUE_READ)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA1

                      RESIDENT TMP2

                        where FLOOR(DATE_READ)=FLOOR(TODAY()-1)

                        ORDER BY OID_METER, DATE_READ DESC;

                     

                     

                    LEFT JOIN (ANALISE_TMP)

                    LOAD

                      ID_LEITURA,

                        OID_METER,

                        TYPE_CONSTANT,

                        DATE_READ AS DIA2,

                        VALUE_READ,

                        (IF(OID_METER=PEEK(OID_METER),PEEK(VALUE_READ)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA2

                      RESIDENT TMP2

                        where FLOOR(DATE_READ)=FLOOR(TODAY()-2)

                        ORDER BY OID_METER, DATE_READ DESC;

                     

                     

                    Its the same code, if Val0 = Value_read, why when i replace the values change?

                      • Re: Help with a code
                        Dave Riley

                        Hi Eduardo,

                         

                        You may have already resolved this, but the reason is in your first code you create a table with fields ..

                        ID_LEITURA,

                        NAME_SYSTEM,

                        NAME_GROUP,

                        NEIGHB_SYSTEM,

                        CITY_SYSTEM,

                        OID_METER,

                        NAME_TYPE_METER,

                        TYPE_CONSTANT,

                        NAME_UC,

                        ADDRESS_BLOCK_UC,

                        DIA0,

                        Val0,

                        CONSUMODIA0

                         

                        .. and then join these fields to it ...

                        ID_LEITURA,

                        OID_METER,

                        TYPE_CONSTANT,

                        DIA1,

                        Val1,

                        CONSUMODIA1

                         

                        ... the 3x common fields are ID_LEITURA, OID_METER & TYPE_CONSTANT and this is how it joins the tables, on a combination of these fields.

                         

                        In the second code you are creating a table with fields ...

                        ID_LEITURA,

                        NAME_SYSTEM,

                        NAME_GROUP,

                        NEIGHB_SYSTEM,

                        CITY_SYSTEM,

                        OID_METER,

                        NAME_TYPE_METER,

                        TYPE_CONSTANT,

                        NAME_UC,

                        ADDRESS_BLOCK_UC,

                        DIA0,

                        VALUE_READ,

                        CONSUMODIA0

                         

                        .. and then join these fields to it ...

                        ID_LEITURA,

                        OID_METER,

                        TYPE_CONSTANT,

                        DIA1,

                        VALUE_READ,

                        CONSUMODIA1

                         

                        .. there are now FOUR common fields ID_LEITURA, OID_METER, TYPE_CONSTANT & VALUE_READ. You will only return data if VALUE_READ for DIA0 is the same as DIA1 which I guess isn't what you intended.

                         

                         

                         

                        flipside