Travel Time

[goro2010]

Hi Everyone,

I am trying to work out the travel time between two destinations.

Please note that the AccountId does differ, I want to take the next customer's "TimeIn" and deduct it from the previous customers's "TimeOut", will this be possible?

Thanks

[Gysbert_Wassenaar · Accepted solution]

You can calculate this in the script using the previous function. Something like interval(TimeIn-previous(TimeOut),'hh:mm:ss') as TravelTime. It's not clear to me if the table should be sorted first on one or more fields (like accountid). If that's necessary you'll need to use an order by clause with a resident load.

[goro2010]

Hi, I need to order the dates as you can see they are not in order, the account ID and "DateTimeFrom" must be in sync, example 1st, 2nd, 3rd ect.

Will this be possible to sort correctly?

Thanks

[goro2010]

Hi,

I do not get my resident load to work 100%. Here is my script below:

"V_OBJ_VisitEvent2":

LOAD DateTimeFrom,
DateTimeTo,
Date(floor(Timestamp#(DateTimeFrom,'YYYY-MM-DD hh:mm:ss')),'DD/MM/YYYY') as [Visit Date],
Time(Frac(Timestamp#(DateTimeFrom,'YYYY-MM-DD hh:mm:ss')),'hh:mm:ss') as TimeIn,
Time(Frac(Timestamp#(DateTimeTo,'YYYY-MM-DD hh:mm:ss')),'hh:mm:ss') as TimeOut,
Interval(DateTimeFrom-Previous(DateTimeTo),'hh:mm:ss') as [Travel Time]


RESIDENT  "V_OBJ_VisitEvent"

Order By DateTimeFrom, AccountId;

Any idea why?

Thanks

[Gysbert_Wassenaar]

That's hard to tell without looking at your document. Perhaps you only need to change to order in the order by clause

Order By AccountId, DateTimeFrom;

And maybe change the expression that calculates Travel Time to:

if(Previous(AccountId)=AccountId,Interval(DateTimeFrom-Previous(DateTimeTo),'hh:mm:ss')) as [Travel Time]

[goro2010]

Is there another way that you need to sort a date table, as mine is YY-MM-DD mm:hh:ss that needs to be sorted , these dates must be ASC.

[goro2010]

Hi Gysbert,

I got right thanks.

Here is my script below:

"V_OBJ_VisitEvent":

SQL SELECT  AccountId,
DateTimeFrom,
DateTimeTo,
Duration,
EventId,
PersonId
FROM "Datasource".dbo."V_OBJ_VisitEvent";

STORE "V_OBJ_VisitEvent" INTO '$(vDataQvds)V_OBJ_VisitEvent.qvd' (qvd);



"V_OBJ_VisitEvent2":

LOAD DateTimeFrom,
DateTimeTo,
Date(floor(Timestamp#(DateTimeFrom,'YYYY-MM-DD hh:mm:ss')),'DD/MM/YYYY') as [Visit Date],
Time(Frac(Timestamp#(DateTimeFrom,'YYYY-MM-DD hh:mm:ss')),'hh:mm:ss') as TimeIn,
Time(Frac(Timestamp#(DateTimeTo,'YYYY-MM-DD hh:mm:ss')),'hh:mm:ss') as TimeOut


RESIDENT  "V_OBJ_VisitEvent";


"V_OBJ_VisitEvent3":

NoConcatenate

LOAD *
RESIDENT "V_OBJ_VisitEvent";

LEFT JOIN ("V_OBJ_VisitEvent3")

LOAD *
RESIDENT "V_OBJ_VisitEvent2";

DROP TABLES "V_OBJ_VisitEvent2", "V_OBJ_VisitEvent";

STORE "V_OBJ_VisitEvent3" INTO '$(vDataQvds)V_OBJ_VisitEvent3.qvd' (qvd);

Drop Table "V_OBJ_VisitEvent3";

TMPVisitEvents:

NoConcatenate

LOAD *

FROM
$(vDataQvds)V_OBJ_VisitEvent3.qvd
(qvd);

Event2:

NoConcatenate

Load *

Resident TMPVisitEvents

Order By DateTimeFrom,DateTimeTo,AccountId,PersonId,[Visit Date];

STORE Event2 INTO '$(vDataQvds)Event2.qvd' (qvd);

Drop Table TMPVisitEvents;

"VisitEvents":

LOAD *,
    if(Previous(PersonId)=PersonId, Interval(DateTimeFrom-Previous(DateTimeTo),'hh:mm:ss')) as [Travel Time]

RESIDENT  "Event2";

Drop Table Event2;