17 Replies Latest reply: Dec 19, 2016 8:29 AM by Eduardo DImperio RSS

    Field not found

    Eduardo DImperio

      Hi everyone,

       

      Im need to select all oid_meter's that consume was not zero, for that i create a variable that receive the consume zero (Exclusao:)

      and select oid_meter not select what's in Exclusao.

       

      Exclusao:

      Load

      DISTINCT OID_METER AS EXCLUDE_OID_METER,

      HORA

      RESIDENT ANALISE_TMP

      WHERE CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0;

       

      INNER JOIN(Exclusao)

       

      ANALISE:

      Load

        HORA,

          ID_LEITURA,

          NAME_SYSTEM,

          NAME_GROUP,

          NEIGHB_SYSTEM,

          CITY_SYSTEM,

          OID_METER,

          NAME_TYPE_METER,

          TYPE_CONSTANT,

          NAME_UC,

          ADDRESS_BLOCK_UC,

          DATE_READ,

          VALUE_READ,

          CONSUMODIA0,

          CONSUMODIA1,

          CONSUMODIA2,

         EXCLUDE_OID_METER

          RESIDENT ANALISE_TMP

          WHERE OID_METER <> EXCLUDE_OID_METER

          ORDER BY OID_METER;

       

      but i receive this error:

      Ocorreram os seguintes erros:

      Field not found - <EXCLUDE_OID_METER>

      I think the EXCLUDE_OID_METER is already created in exclusao, but maybe the inner join have something to do with.

        • Re: Field not found
          Sunny Talwar

          Does your ANALISE_TMP table include both EXCLUDE_OID_METER and OID_METER? or one of the two?

            • Re: Field not found
              Eduardo DImperio

              Hey sunny,

               

              Just one

               

              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,

                  VALUE_READ,

                  Date(DATE_READ) AS HORA,

                  If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT)   as CONSUMODIA0,

                  If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-1),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA1,

                  If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-2),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA2

                RESIDENT TMP2

                  ORDER BY OID_METER, DATE_READ DESC;

               

              DROP TABLE TMP,TMP2;

               

              Exclusao:

              Load

              DISTINCT OID_METER AS EXCLUDE_OID_METER,

              HORA

              RESIDENT ANALISE_TMP

              WHERE CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0;

               

               

              INNER JOIN(Exclusao)

               

              ANALISE:

               

              Load

                HORA,

                  ID_LEITURA,

                  NAME_SYSTEM,

                  NAME_GROUP,

                  NEIGHB_SYSTEM,

                  CITY_SYSTEM,

                  OID_METER,

                  NAME_TYPE_METER,

                  TYPE_CONSTANT,

                  NAME_UC,

                  ADDRESS_BLOCK_UC,

                  DATE_READ,

                  VALUE_READ,

                  CONSUMODIA0,

                  CONSUMODIA1,

                  CONSUMODIA2,

                  EXCLUDE_OID_METER

                  RESIDENT ANALISE_TMP

                  WHERE OID_METER <> EXCLUDE_OID_METER

                  ORDER BY OID_METER;

                • Re: Field not found
                  Sunny Talwar

                  So, if you don't have EXCLUDE_OID_METER in your table, how can you refer it in the resident load.

                   

                  ANALISE:

                  Load

                    HORA,

                      ID_LEITURA,

                      NAME_SYSTEM,

                      NAME_GROUP,

                      NEIGHB_SYSTEM,

                      CITY_SYSTEM,

                      OID_METER,

                      NAME_TYPE_METER,

                      TYPE_CONSTANT,

                      NAME_UC,

                      ADDRESS_BLOCK_UC,

                      DATE_READ,

                      VALUE_READ,

                      CONSUMODIA0,

                      CONSUMODIA1,

                      CONSUMODIA2,

                    EXCLUDE_OID_METER

                    OID_METER,

                      RESIDENT ANALISE_TMP

                      WHERE OID_METER <> EXCLUDE_OID_METER

                      ORDER BY OID_METER;

                   

                  Not sure I understand what you are trying to do here ->   WHERE OID_METER <> EXCLUDE_OID_METER

                    • Re: Field not found
                      Eduardo DImperio

                      Hum, i thought that once i have a join i can use the field of that join.

                       

                      The problem persist with the modification

                       

                      How can i use the values of EXCLUDE_OID_METER to exclude in OID_METER?

                       

                      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,

                          VALUE_READ,

                          Date(DATE_READ) AS HORA,

                          If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT)   as CONSUMODIA0,

                          If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-1),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA1,

                          If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-2),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA2

                        RESIDENT TMP2

                          ORDER BY OID_METER, DATE_READ DESC;

                       

                       

                      DROP TABLE TMP,TMP2;

                       

                       

                       

                      Exclusao:

                      Load

                      DISTINCT OID_METER AS EXCLUDE_OID_METER,

                      HORA

                      RESIDENT ANALISE_TMP

                      WHERE CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0;

                       

                       

                      INNER JOIN(Exclusao)

                       

                       

                      ANALISE:

                       

                       

                      Load

                        HORA,

                          ID_LEITURA,

                          NAME_SYSTEM,

                          NAME_GROUP,

                          NEIGHB_SYSTEM,

                          CITY_SYSTEM,

                          OID_METER,

                          NAME_TYPE_METER,

                          TYPE_CONSTANT,

                          NAME_UC,

                          ADDRESS_BLOCK_UC,

                          DATE_READ,

                          VALUE_READ,

                          CONSUMODIA0,

                          CONSUMODIA1,

                          CONSUMODIA2

                          RESIDENT ANALISE_TMP

                          WHERE

                          OID_METER <> EXCLUDE_OID_METER

                          ORDER BY OID_METER, HORA DESC;

                • Re: Field not found
                  Sunny Talwar

                  Why don't you just do this?

                   

                  ANALISE_TMP:

                  LOAD *

                  Where Not(CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0);

                  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,

                      VALUE_READ,

                      Date(DATE_READ) AS HORA,

                      If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT)  as CONSUMODIA0,

                      If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-1),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA1,

                      If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-2),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA2

                    RESIDENT TMP2

                      ORDER BY OID_METER, DATE_READ DESC;

                    • Re: Field not found
                      Eduardo DImperio

                      Because if i do Where Not(CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0); I'll eliminate only the hour that measure had happen, but not the oid_meter responsible for that.

                       

                      The logic is, if my oid_meter find some value = 0 in any hour of the day we discard him.

                      Like if 19:00 of (today -1)  the oid_meter 1234 got a zero, we dont want to see any other measure of that meter in today-2, today-1 or today.

                       

                      got it?

                        • Re: Field not found
                          Sunny Talwar

                          How about this?

                           

                          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,

                              VALUE_READ,

                              Date(DATE_READ) AS HORA,

                              If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT)  as CONSUMODIA0,

                              If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-1),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA1,

                              If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-2),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA2

                            RESIDENT TMP2

                              ORDER BY OID_METER, DATE_READ DESC;

                           

                          DROP TABLE TMP,TMP2;

                           

                          Right Join (ANALISE_TMP)

                          LOAD DISTINCT OID_METER

                          RESIDENT ANALISE_TMP

                          WHERE NOT(CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0);

                           

                          Not sure if you want to remove just the particular HORA also, but assuming you want to completely get rid of all OID_METER if the condition is met once, then try the above

                            • Re: Field not found
                              Eduardo DImperio

                              Hey Sunny,

                               

                              Dont work, i did a simple test, print some oid_meters, oid_meter_exclude and oid_meter after right join.

                               

                              I took oid_meters 87065 and 87066 present in Exclude_Oid_meter, but they still remain after right join.

                               

                              Exclude_Meter.JPGOID_Meter.JPGOID_Meter_right_join.JPG

                                • Re: Field not found
                                  Sunny Talwar

                                  How are you still getting EXCLUDE_OID_METER? I changed the right join table to this:

                                   

                                  Right Join (ANALISE_TMP)

                                  LOAD DISTINCT OID_METER

                                  RESIDENT ANALISE_TMP

                                  WHERE NOT (CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0);

                                   

                                  No more EXCLUDE_OID_METER

                                    • Re: Field not found
                                      Eduardo DImperio

                                      Yeah, let me explain

                                      The OID_METER.jpg, was a select of all oid_meters

                                      The EXCLUDE_OID_METER.jpg  was a select of all oid_meters with consumodia=0, i print the meters 84065 and 84066 for example with this code:

                                       

                                      Exclusao:

                                      LOAD

                                      OID_METER AS EXCLUDE_OID_METER

                                      RESIDENT ANALISE_TMP

                                      WHERE CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0;

                                       

                                      After  that i replace that code for your:

                                       

                                      Right Join (ANALISE_TMP)

                                      LOAD DISTINCT OID_METER

                                      RESIDENT ANALISE_TMP

                                      WHERE NOT (CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0);

                                       

                                      and got the last print OID_METER_RIGHT_JOIN, but the meters 84065 and 84066 still remain.





                                        • Re: Field not found
                                          Sunny Talwar

                                          I understand your situation now.... Let me think about it a little

                                           

                                          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,

                                              VALUE_READ,

                                              Date(DATE_READ) AS HORA,

                                              If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT)  as CONSUMODIA0,

                                              If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-1),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA1,

                                              If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-2),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA2

                                            RESIDENT TMP2

                                              ORDER BY OID_METER, DATE_READ DESC;

                                           

                                          DROP TABLE TMP,TMP2;

                                           

                                          Exclusao:

                                          LOAD DISTINCT OID_METER AS EXCLUDE_OID_METER,

                                          HORA

                                          RESIDENT ANALISE_TMP

                                          WHERE CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0;

                                           

                                          INNER JOIN(Exclusao)

                                           

                                           

                                          ANALISE:

                                          LOAD

                                            HORA,

                                              ID_LEITURA,

                                              NAME_SYSTEM,

                                              NAME_GROUP,

                                              NEIGHB_SYSTEM,

                                              CITY_SYSTEM,

                                              OID_METER,

                                              NAME_TYPE_METER,

                                              TYPE_CONSTANT,

                                              NAME_UC,

                                              ADDRESS_BLOCK_UC,

                                              DATE_READ,

                                              VALUE_READ,

                                              CONSUMODIA0,

                                              CONSUMODIA1,

                                              CONSUMODIA2

                                              RESIDENT ANALISE_TMP

                                              WHERE NOT EXISTS(EXCLUDE_OID_METER, OID_METER)

                                              ORDER BY OID_METER, HORA DESC;

                                        • Re: Field not found
                                          Eduardo DImperio

                                          I did this to show better:

                                           

                                          Right Join (ANALISE_TMP)

                                           

                                           

                                          LOAD DISTINCT OID_METER

                                          RESIDENT ANALISE_TMP

                                          WHERE NOT(CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0);

                                           

                                          Teste:

                                          Example.JPG

                                          LOAD

                                          OID_METER,

                                          CONSUMODIA0,

                                          CONSUMODIA1,

                                          CONSUMODIA2

                                          RESIDENT ANALISE_TMP

                                          WHERE OID_METER=87066;

                                           

                                          DROP TABLE ANALISE_TMP;

                                           

                                          This meter should not appear anymore in OID_METER

                                            • Re: Field not found
                                              Sunny Talwar

                                              Check the script above, I think I understand your situation now....

                                                • Re: Field not found
                                                  Eduardo DImperio

                                                  lol  this code crashs the QS in the server !

                                                   

                                                  I did 3 tests

                                                   

                                                  With the clause Where and with Inner Join - Crash the system

                                                  Without clause where and without Inner Join - Runs Forever

                                                  Whitout Inner Join and with clause Where  - Runs ok, but code doesnt work

                                                   

                                                  Any Idea why this happen?Erro.JPG

                                                  Translate: Connection to the Qlik Sense engine failed for unspecified reasons. Please update your browser or contact your system administrator

                                                  • Re: Field not found
                                                    Eduardo DImperio

                                                    I think i found a trick.

                                                     

                                                    I create a flag and will try filter by it

                                                     

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

                                                     

                                                    but a strange thing is happening

                                                     

                                                    the compiler is not recognizing my table.

                                                     

                                                     

                                                    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,

                                                        VALUE_READ,

                                                        Date(DATE_READ) AS HORA,

                                                        If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT)  as CONSUMODIA0,

                                                        If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-1),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA1,

                                                        If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-2),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA2,

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

                                                      RESIDENT TMP2

                                                        ORDER BY OID_METER, DATE_READ DESC;

                                                     

                                                    DROP TABLE TMP,TMP2;

                                                     

                                                     

                                                    Analise:

                                                    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,

                                                        VALUE_READ,

                                                        HORA,

                                                        CONSUMODIA0,

                                                        CONSUMODIA1,

                                                        CONSUMODIA2,

                                                        FLAG

                                                        RESIDENT ANALISE_TMP

                                                        WHERE FLAG=1

                                                        ORDER BY OID_METER, HORA DESC;

                                                     

                                                     

                                                    DROP TABLE ANALISE_TMP;

                                                     

                                                    Any idea why?Erro_Table.JPG