3 Replies Latest reply: Apr 5, 2017 8:42 AM by Eduardo DImperio RSS

    HELP WITH MIN() AND MAX()

    Eduardo DImperio

      Hi people

       

      I need some help about Min() and Max() function

       

      in the code below i hope to get only one value, in this case the max value, but i receive more then that.

       

      CONCAT_TABLE:

      LOAD

      *

      FROM [lib://Dados/MI/Leituras/Individual_Read/RS_INDIVIDUAL_READ_20170319.qvd](qvd);

       

       

      LOAD

      *

      FROM [lib://Dados/MI/Leituras/Individual_Read/RS_INDIVIDUAL_READ_20170404.qvd](qvd);

       

       

      NoConcatenate

       

      EXTRAI_DATA:

      LOAD

      ID_LEITURA,

      MAX(DATE_READ) AS DATA_FIM

      Resident CONCAT_TABLE

      WHERE FLOOR(DATE_READ)='19/03/2017' OR FLOOR(DATE_READ)='04/04/2017'

      GROUP BY

      ID_LEITURA,

      DATE_READ;

       

       

      DROP TABLE CONCAT_TABLE;

       

       

      EXIT SCRIPT;

       

       

      Min.JPG

       

       

      Thank you all

        • Re: HELP WITH MIN() AND MAX()
          Andrey Khoronenko

          Hi Eduardo,

           

          May be this

           

          CONCAT_TABLE:

          LOAD

          *

          FROM [lib://Dados/MI/Leituras/Individual_Read/RS_INDIVIDUAL_READ_20170319.qvd](qvd);

           

           

          LOAD

          *

          FROM [lib://Dados/MI/Leituras/Individual_Read/RS_INDIVIDUAL_READ_20170404.qvd](qvd);

           

           

          NoConcatenate

           

          EXTRAI_DATA:

          LOAD

          ID_LEITURA,

          MAX(DATE_READ) AS DATA_FIM

          Resident CONCAT_TABLE

          WHERE (FLOOR(DATE_READ)='19/03/2017' OR FLOOR(DATE_READ)='04/04/2017') AND ID_LEITURA='20|1|0'

          GROUP BY

          ID_LEITURA,

          DATE_READ;

           

           

          DROP TABLE CONCAT_TABLE;

           

           

          EXIT SCRIPT;

           

          Regards,

          Andrey

          • Re: HELP WITH MIN() AND MAX()
            Eduardo DImperio

            I found the problem, I can not use group by to variable that i want to get a measure like max or min. So simple that i didn't saw it.

             

            CONCAT_TABLE:

            LOAD

            *

            FROM [lib://Dados/MI/Leituras/Individual_Read/RS_INDIVIDUAL_READ_20170319.qvd](qvd);

             

             

            LOAD

            *

            FROM [lib://Dados/MI/Leituras/Individual_Read/RS_INDIVIDUAL_READ_20170404.qvd](qvd);

             

             

            NoConcatenate

             

            EXTRAI_DATA:

            LOAD

            ID_LEITURA,

            MAX(DATE_READ) AS DATA_FIM

            Resident CONCAT_TABLE

            WHERE FLOOR(DATE_READ)='19/03/2017' OR FLOOR(DATE_READ)='04/04/2017'

            GROUP BY

            ID_LEITURA,

            DATE_READ;

             

             

            DROP TABLE CONCAT_TABLE;

             

             

            EXIT SCRIPT;