topic Trip duration from Log Dates in App Development

Trip duration from Log Dates

johngouws — Fri, 01 Mar 2019 03:49:31 GMT

Good Day. The below data shows an extract of a track log for trips. What I need to determine is the number of 'Trip Days'. For example, for trip aaaa all the dates of the 27th will be day 1, the 29th will be day 2, the 30th will be day 3 and so on. I need to end up with trip aaaa being 4 days and trip bbbb 3 days. The issue I am having is that I cannot get the counter variable to step correctly. The attached qvf has the script I am using. I appreciate the assistance.

Trip, Time
aaaa, 2018-07-27 06:08
aaaa, 2018-07-27 06:08
aaaa, 2018-07-27 06:08
aaaa, 2018-07-27 06:08
aaaa, 2018-07-27 06:08
aaaa, 2018-07-27 06:08
aaaa, 2018-07-27 19:59
aaaa, 2018-07-29 07:49
aaaa, 2018-07-29 07:49
aaaa, 2018-07-29 07:50
aaaa, 2018-07-29 07:50
aaaa, 2018-07-29 07:50
aaaa, 2018-07-30 15:03
aaaa, 2018-07-30 15:03
aaaa, 2018-07-30 15:03
aaaa, 2018-07-30 15:04
aaaa, 2018-07-31 08:12
aaaa, 2018-07-31 08:12
aaaa, 2018-07-31 08:12
aaaa, 2018-07-31 08:12
bbbb, 2018-07-27 06:08
bbbb, 2018-07-27 06:08
bbbb, 2018-07-27 06:08
bbbb, 2018-07-27 06:08
bbbb, 2018-07-27 06:08
bbbb, 2018-07-30 15:03
bbbb, 2018-07-30 15:04
bbbb, 2018-07-31 08:12
bbbb, 2018-07-31 08:12
bbbb, 2018-07-31 08:12
bbbb, 2018-07-31 08:12];

Re: Trip duration from Log Dates

jonathandienst — Fri, 01 Mar 2019 05:04:25 GMT

Extract the Date from the date/time value and use the extended autonumber for this:

t1:
LOAD Trip,	
    Date(Floor(Date#(Time, 'yyyy-MM-dd hh:mm'))) as Date,
    Time(Frac(Date#(Time, 'yyyy-MM-dd hh:mm'))) as Time 
INLINE [ 
Trip,	Time
aaaa,	2018-07-27 06:08
aaaa,	2018-07-27 06:08
...<snip>...
bbbb,	2018-07-31 08:12];

t2:
LOAD 
    Trip,
    Date, 
    Time,
    AutoNumber(Date, Trip) as [Trip Day]
Resident t1 

Drop Table t

Re: Trip duration from Log Dates

jonathandienst — Fri, 01 Mar 2019 05:15:27 GMT

Re: Trip duration from Log Dates

johngouws — Fri, 01 Mar 2019 05:34:28 GMT

Thank you Jonty. Works perfectly. Who could have thought I could be so far off.