topic Re: Travel Time in QlikView

Travel Time

goro2010 — Tue, 19 Mar 2013 10:08:14 GMT

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

Re: Travel Time

Gysbert_Wassenaar — Tue, 19 Mar 2013 11:02:43 GMT

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.

Re: Travel Time

goro2010 — Tue, 19 Mar 2013 11:10:40 GMT

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

Re: Travel Time

goro2010 — Tue, 19 Mar 2013 12:24:35 GMT

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

Re: Travel Time

Gysbert_Wassenaar — Tue, 19 Mar 2013 12:29:19 GMT

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]

Re: Travel Time

goro2010 — Tue, 19 Mar 2013 12:32:39 GMT

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.

Re: Travel Time

goro2010 — Tue, 19 Mar 2013 13:27:06 GMT

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;