Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculating difference between times

I have a series of data (to the order of 100,000+ rows of data which denotes the date and time an action was performed. The LOAD statement pulls in four different pieces of data and the structure looks like this:

IncidentUnitDateTimeActionTaken
123415/12/2015 18:34:15D
123415/12/2015 18:35:00E
123415/12/2015 18:38:27A
123415/12/2015 19:07:32C

I need to calculate the amount of time elapsed between actions D and E, E and A, D and A and D and C so that I can look at a specific incident and then report only what the time differences are.

I really don't have the first clue where to start.

Additionally, some of the times may not exist (for example, a unit may not perform action E) and in these cases the TimeStamp is set to 0.

5 Replies
MK_QSL
MVP
MVP

Temp:

Load

  Incident,

  Unit,

  TimeStamp(Timestamp#(DateTime,'D/M/YYYY hh:mm:ss')) as DateTime,

  If(ActionTaken = 'D', 1,

  If(ActionTaken = 'E', 2,

  If(ActionTaken = 'A', 3,

  If(ActionTaken = 'C', 4)))) as ActionFlag

Inline

[

  Incident, Unit, DateTime, ActionTaken

  1234, 1, 5/12/2015 18:34:15, D

  1234, 1, 5/12/2015 18:35:00, E

  1234, 1, 5/12/2015 18:38:27, A

  1234, 1, 5/12/2015 19:07:32, C

  3456, 1, 5/12/2015 18:34:15, D

  3456, 1, 5/12/2015 18:38:27, A

  3456, 1, 5/12/2015 19:07:32, C

  6789, 1, 5/12/2015 18:34:15, D

  6789, 1, 5/12/2015 18:35:00, E

  6789, 1, 5/12/2015 19:07:32, C

];

Final:

Load

  *,

  If(ActionFlag = 1, 0) as ActionStart,

  If(ActionFlag = 2 and Previous(ActionFlag) = 1, Interval(DateTime - Previous(DateTime),'hh:mm:ss'),0) as [E-D],

  If(ActionFlag = 3 and Previous(ActionFlag) = 2, Interval(DateTime - Previous(DateTime),'hh:mm:ss'),0) as [A-E],

  If(ActionFlag = 3 and Previous(Previous(ActionFlag)) = 1, Interval(DateTime - Previous(Previous(DateTime)),'hh:mm:ss'),0) as [A-D]

Resident Temp

Order By Incident, ActionFlag;

Drop Table Temp;

Same way you can create D-C and others...

Hint : Use

Previous(Previous(Previous(ActionFlag)))

Not applicable
Author

I attempted to adapt your code to my system but I can't seem to get it to work. It loads the data but isn't calculating the times. The times are always 0. When I use your example, it works as expected. My script follows:

Temp:

Load

  "incident_num" as Incident,

  "unit_id" as Unit,

  TimeStamp(Timestamp#("unit_status_datetime",'D/M/YYYY hh:mm:ss')) as DateTime,

  If("cad_command_code" = 'D', 1,

  If("cad_command_code" = 'E', 2,

  If("cad_command_code" = 'A', 3,

  If("cad_command_code" = 'C', 4)))) as ActionFlag;

SQL SELECT "incident_num",

    "unit_id",

    "unit_status_datetime",

    "cad_command_code"

FROM nfirs.dbo."unit_status_hist"

WHERE "incident_num" like '1500001%';

Final:

Load

  *,

  If(ActionFlag = 1, 0) as ActionStart,

  If(ActionFlag = 2 and Previous(ActionFlag) = 1, Interval(DateTime - Previous(DateTime),'hh:mm:ss'),0) as [E-D],

  If(ActionFlag = 3 and Previous(ActionFlag) = 2, Interval(DateTime - Previous(DateTime),'hh:mm:ss'),0) as [A-E],

  If(ActionFlag = 3 and Previous(Previous(ActionFlag)) = 1, Interval(DateTime - Previous(Previous(DateTime)),'hh:mm:ss'),0) as [A-D]

Resident Temp

Order By Incident, ActionFlag;

Drop Table Temp;

maxgro
MVP
MVP

you can join the rows in a single row with all datetime

z:

Load 

  Incident, 

  Unit, 

  TimeStamp(Timestamp#(DateTime,'D/M/YYYY hh:mm:ss')) as DateTime, 

  ActionTaken 

Inline 

  Incident, Unit, DateTime, ActionTaken 

  1234, 1, 5/12/2015 18:34:15, D 

  1234, 1, 5/12/2015 18:35:00, E 

  1234, 1, 5/12/2015 18:38:27, A 

  1234, 1, 5/12/2015 19:07:32, C 

  3456, 1, 5/12/2015 18:34:15, D 

  3456, 1, 5/12/2015 18:38:27, A 

  3456, 1, 5/12/2015 19:07:32, C 

  6789, 1, 5/12/2015 18:34:15, D 

  6789, 1, 5/12/2015 18:35:00, E 

  6789, 1, 5/12/2015 19:07:32, C 

]; 

y:

load Incident, Unit, DateTime as DateTimeD Resident z where ActionTaken = 'D';

join (y) load Incident, Unit, DateTime as DateTimeE Resident z where ActionTaken = 'E';

join (y) load Incident, Unit, DateTime as DateTimeA Resident z where ActionTaken = 'A';

join (y) load Incident, Unit, DateTime as DateTimeC Resident z where ActionTaken = 'C';

DROP Table z;

ToniKautto
Employee
Employee

Attached is a simple example of how to find the smallest timestamp over Incident and Unit, and the use this to compare with the timestamp on each row. To show the difference you can use the Interval() function.

Not applicable
Author

So here's what I've got so far...

Temp:

LOAD "incident_num" as Incident,

    "unit_id" as Unit,

    If("cad_command_code" = 'D', timestamp("unit_status_datetime",'MM/DD/YYYY hh:mm:ss')) as DispatchTime,

      If("cad_command_code" = 'E', timestamp("unit_status_datetime",'MM/DD/YYYY hh:mm:ss')) as EnrouteTime,

      If("cad_command_code" = 'A', timestamp("unit_status_datetime",'MM/DD/YYYY hh:mm:ss')) as ArriveTime,

      If("cad_command_code" = 'C', timestamp("unit_status_datetime",'MM/DD/YYYY hh:mm:ss')) as ClearTime,

    "situation_found_code" as "Situation Found Code",

    "situation_found_code"&' - '&"SFText" as "Situation Found",

    "district_code" as "District";

SQL SELECT "fire_incident_core"."situation_found_code",

    "fire_incident_core"."district_code",

    "fire_incident_core"."shift_code",

    "fire_incident_core"."incident_num",

    "unit_status_hist"."unit_id",

    "unit_status_hist"."unit_status_datetime",

    "unit_status_hist"."cad_command_code",

    "codes_table"."description" as "SFText"

FROM nfirs.dbo."fire_incident_core"

LEFT OUTER JOIN nfirs.dbo."unit_status_hist" ON "fire_incident_core"."incident_num" = "unit_status_hist"."incident_num"

LEFT OUTER JOIN nfirs.dbo."codes_table" ON "fire_incident_core"."situation_found_code"="codes_table"."code"

WHERE "codes_table"."code_group" = '0000';

   

Final:

Load

  *,

  Interval(EnrouteTime - DispatchTime,'ss') as Turnout,

  Interval(ArriveTime-EnrouteTime) as Travel,

  Interval(ArriveTime-DispatchTime) as Response

Resident Temp

Order By Incident;

Drop Table Temp;

I can use an expression to calculate the time between "enroute" and "dispatch"; however, if I try to pull it up in the load script, I don't get any data.

Any ideas?