4 Replies Latest reply: Dec 15, 2016 7:08 AM by Eduardo DImperio RSS

    Getting max date in a range

    Eduardo DImperio

      Hi everyone,

       

      Im trying to complete a field that show the current date load, not the current system date, but the the file.

       

      So i did 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,

          Max(Date(DATE_READ))AS Hora_referencia,

          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;

       

      Ocorreram os seguintes erros:

      Invalid expression

      O erro ocorreu aqui:

      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, Max(Date(DATE_READ))AS Hora_referencia, 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

       

      Why can i use Date(DATE_READ) AS HORA but not Max(Date(DATE_READ))AS Hora_referencia?

      By the way that some how to put this value in a variable and use in the grafic?