Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;