2 Replies Latest reply: Jul 10, 2017 9:44 AM by Sunny Talwar RSS

    Select Max Date for unique field value

    Harjit Nar

      Hi,

       

      I have a table that looks similar to this:

       

      User IDDate
      101/10/16
      102/10/16
      104/10/16
      107/10/16
      201/10/16
      203/10/16
      204/10/16
      301/10/16

       

      I would like to select both the MIN/MAX date for each user ID at load. Does anyone know how to do this? (within the script)

        • Re: Select Max Date for unique field value
          Justin Dallas

          Try this.

           

          LOAD [User ID], Max(Date), Min(Date)
          GROUP BY [User ID]
          
          

           

           

          A demo script of the statement in action is below

           

          Demo:
          DatesTable:
          LOAD [User ID], Max(Date), Min(Date)
          GROUP BY [User ID]
          ;
          LOAD * Inline
          [
            'User ID' , Date
            1 , 01/10/16
            1 , 02/10/16
            1 , 04/10/16
            1 , 07/10/16
            2 , 01/10/16
            2 , 03/10/16
            2 , 04/10/16
            3 , 01/10/16
          ]
          ;
          
          
          EXIT Script
          ;
          
          
          • Re: Select Max Date for unique field value
            Sunny Talwar

            May be this

             

            Table:

            LOAD [User ID],

                 Date,

                 OtherFields

            FROM ....

             

            TempTable:

            NoConcatenate

            LOAD  [User ID],

                 Min(Date) as Date

            Resident Table

            Group By [User ID];

             

            Concatenate (TempTable)

            LOAD [User ID],

                 Max(Date) as Date

            Resident Table

            Group By [User ID];


            Right Join (Table)

            LOAD *

            Resident TempTable;


            DROP Table TempTable;