1 Reply Latest reply: Oct 25, 2017 5:07 PM by Stefan Wühl RSS

    Create / Load new table with maximum and minimum records

    Rodrigo Serrano

      I have a table of trips with different stops on each trip (numbered in sequence) with the time of departure and arrival at each stop.

       

      TABLE:

      Trip, Sequence, Departure, Arrival

      1,1,10:13,10:25

      1,2,10:29,10:35

       

      2,1,10:15,11:05

      2,2,11:20,11:32

      2,3,11:55,12:36

       

      3,1,10:00,10:25

      3,2,10:36,10:45

      3,3,11:06,11:24

       

      4,1,10:01,10:15

      4,2,10:35,10:45

      4,3,10:56,11:14

      4,4,11:35,11:46

      4,5,11:52,11:59

       

      For each trip, I only need the departure time of the first sequence and the arrival time of the last sequence:

       

      TABLE:

      Trip, Departure, Arrival

      1,10:13,10:35

      2,10:15,12:36

      3,10:00,11:24

      4,10:01,11:59

       

      Many thanks to the community for their help

        • Re: Create / Load new table with maximum and minimum records
          Stefan Wühl

          INPUT:

          LOAD * INLINE [

          Trip, Sequence, Departure, Arrival

          1,1,10:13,10:25

          1,2,10:29,10:35

           

          2,1,10:15,11:05

          2,2,11:20,11:32

          2,3,11:55,12:36

           

          3,1,10:00,10:25

          3,2,10:36,10:45

          3,3,11:06,11:24

           

          4,1,10:01,10:15

          4,2,10:35,10:45

          4,3,10:56,11:14

          4,4,11:35,11:46

          4,5,11:52,11:59

          ];

           

           

          RESULT:

          LOAD Trip, FirstSortedValue(Departure, Sequence) as Departure, FirstSortedValue(Arrival,-Sequence) as Arrival

          Resident INPUT

          GROUP BY Trip;

           

           

          DROP Table INPUT;