Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I've got the following table:
id | expedition-id | vehicle-id | time | latitude | longitude |
1 | 400 | 1 | 11/4/21 09.00 | 41.1 | 28.0 |
2 | 400 | 1 | 11/4/21 12.00 | 41.0 | 28.0 |
3 | 401 | 2 | 11/5/21 08.30 | 41.4 | 28.4 |
5 | 401 | 2 | 11/5/21 14.00 | 41.3 | 28.2 |
6 | 402 | 3 | 11/6/21 09.30 | 41.8 | 28.7 |
7 | 402 | 3 | 11/6/21 14.30 | 41.7 | 28.6 |
8 | 403 | 4 | 11/7/21 09.00 | 41.5 | 28.9 |
What I'm trying to do is group by expedition-id and show the most recent date.
id | expedition-id | vehicle-id | time | latitude | longitude |
1 | 400 | 1 | 11/4/21 12.00 | x | y |
2 | 401 | 2 | 11/5/21 14.00 | x | y |
3 | 402 | 3 | 11/6/21 14.30 | x | y |
4 | 403 | 4 | 11/7/21 09.00 | x | y |
My goal is get the recent location of shipping vehicles. How can I do this? Thanks in advance.
Hi you can do a group by by the field that doesn't changes, and use an aggretion expression for the other fields, in this case I don't know what you want to do with latitude and longitude, wich can have different values for each expedition-id.
To retunr the most recent date you can use max(), this returns a number, with Timstam() you can give a timestamp format.
There is also the option to use FirstValue as aggregation function, to retrieve the first loaded value.
In summary it could be something like:
LOAD
expedition-id,
FirstValue(id) as id,
FirstValue(vehicle-id) as vehicle-id,
FirstValue(latitude) as latitude,
FirstValue(longitude) as longitude,
Timestamp(Max(time), 'M/D/YY hh.mm') as time
Resident/From...
Group by expedition-id;
Hi you can do a group by by the field that doesn't changes, and use an aggretion expression for the other fields, in this case I don't know what you want to do with latitude and longitude, wich can have different values for each expedition-id.
To retunr the most recent date you can use max(), this returns a number, with Timstam() you can give a timestamp format.
There is also the option to use FirstValue as aggregation function, to retrieve the first loaded value.
In summary it could be something like:
LOAD
expedition-id,
FirstValue(id) as id,
FirstValue(vehicle-id) as vehicle-id,
FirstValue(latitude) as latitude,
FirstValue(longitude) as longitude,
Timestamp(Max(time), 'M/D/YY hh.mm') as time
Resident/From...
Group by expedition-id;