9 Replies Latest reply: Dec 14, 2016 12:15 PM by Eduardo DImperio RSS

    Alighing Fields using Distinct

    Eduardo DImperio

      Hi,

       

      I dont know why my distinct doesnt work.

       

      I have from input 3 Dates of consuming energy, today, today-1 and today -2 and i need put this consuming data by hour, side by side. I try everithing even transform my Data in Date, get the hour of this date and select distinct hour withou sucess.

      Below the code and the image of my output right now and a excel with what a want to be my output.

       

      Help please

       

      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 WHERE OID_METER=70794 AND NAME_UC ='ZELADORIA'

          ORDER BY OID_METER, DATE_READ DESC;

         

      DROP TABLE TMP,TMP2;

       

       

      ANALISE:

      LOAD

          DISTINCT HOUR(HORA) AS HORA0,

        ID_LEITURA,

          NAME_SYSTEM,

          NAME_GROUP,

          NEIGHB_SYSTEM,

          CITY_SYSTEM,

          OID_METER,

          NAME_TYPE_METER,

          NAME_UC,

          ADDRESS_BLOCK_UC,

          CONSUMODIA0,

          CONSUMODIA1,

          CONSUMODIA2

          RESIDENT ANALISE_TMP;

       

      CONSUME.PNGOUTPUT.PNG