3 Replies Latest reply: Jul 27, 2015 1:02 PM by durga seelam RSS

    Help me to find the lowest date?

    durga seelam

      Hi All,

       

      DATE
      12/29/14 1:52:03 PM
      1/5/15 12:12:33 PM
      1/5/15 12:10:33 PM
      1/5/15 12:05:33 PM
      1/5/15 12:25:33 AM
      1/5/15 12:25:33 PM
      1/9/15 11:37:28 AM
      1/9/15 11:37:28 AM
      1/9/15 11:42:28 AM
      1/15/15 12:25:33 PM
      8/9/15 11:37:28 AM
      8/9/15 11:37:28 AM
      10/5/15 12:25:33 PM
      10/5/15 12:22:33 PM
      10/9/15 9:37:28 AM
      10/9/15 10:37:28 AM
      10/9/15 11:37:28 AM
      10/15/15 12:25:33 PM
      10/19/15 11:43:28 AM
      11/5/15 12:25:33 PM
      11/11/15 11:51:28 AM
      11/19/15 12:15:33 PM
      11/25/15 12:25:33 PM

       

      Now my scenario is to find the date having the minimum timestamp for respective dates. My output to be as follow.

       

      DATE
      12/29/14 1:52:03 PM
      1/5/15 12:25:33 AM
      1/9/15 11:37:28 AM
      1/15/15 12:25:33 PM
      8/9/15 11:37:28 AM
      10/5/15 12:22:33 PM
      10/9/15 9:37:28 AM
      10/15/15 12:25:33 PM
      10/19/15 11:43:28 AM
      11/5/15 12:25:33 PM
      11/11/15 11:51:28 AM
      11/19/15 12:15:33 PM
      11/25/15 12:25:33 PM

       

      Please help me in writing expression.

       

      Thanks,

      Durga

        • Re: Help me to find the lowest date?
          Stefan Wühl

          SET TimestampFormat = 'M/D/YY h:mm:ss TT';

           

          LOAD Timestamp(min(DATE)) as DATE INLINE [

          DATE

          12/29/14 1:52:03 PM

          1/5/15 12:12:33 PM

          1/5/15 12:10:33 PM

          1/5/15 12:05:33 PM

          1/5/15 12:25:33 AM

          1/5/15 12:25:33 PM

          1/9/15 11:37:28 AM

          1/9/15 11:37:28 AM

          1/9/15 11:42:28 AM

          1/15/15 12:25:33 PM

          8/9/15 11:37:28 AM

          8/9/15 11:37:28 AM

          10/5/15 12:25:33 PM

          10/5/15 12:22:33 PM

          10/9/15 9:37:28 AM

          10/9/15 10:37:28 AM

          10/9/15 11:37:28 AM

          10/15/15 12:25:33 PM

          10/19/15 11:43:28 AM

          11/5/15 12:25:33 PM

          11/11/15 11:51:28 AM

          11/19/15 12:15:33 PM

          11/25/15 12:25:33 PM

          ]

          GROUP BY floor(DATE);

          • Re: Help me to find the lowest date?
            Sunny Talwar

            Try this script:

             

            Table:

            LOAD DATE as TimeStamp,

              Date(Floor(DATE)) as Date,

              Time(Frac(DATE)) as Time;

            LOAD * Inline [

            DATE

            12/29/14 1:52:03 PM

            1/5/15 12:12:33 PM

            1/5/15 12:10:33 PM

            1/5/15 12:05:33 PM

            1/5/15 12:25:33 AM

            1/5/15 12:25:33 PM

            1/9/15 11:37:28 AM

            1/9/15 11:37:28 AM

            1/9/15 11:42:28 AM

            1/15/15 12:25:33 PM

            8/9/15 11:37:28 AM

            8/9/15 11:37:28 AM

            10/5/15 12:25:33 PM

            10/5/15 12:22:33 PM

            10/9/15 9:37:28 AM

            10/9/15 10:37:28 AM

            10/9/15 11:37:28 AM

            10/15/15 12:25:33 PM

            10/19/15 11:43:28 AM

            11/5/15 12:25:33 PM

            11/11/15 11:51:28 AM

            11/19/15 12:15:33 PM

            11/25/15 12:25:33 PM

            ];

             

            Right Join(Table)

            LOAD Date,

              Min(Time) as Time

            Resident Table

            Group By Date;

             

            Output:

             

            Capture.PNG

            • Re: Help me to find the lowest date?
              durga seelam

              Sunny and Sweuhl, both are working. Thanks for response.