Calculate duration from on/off events

[#1]

Hi

My input data are timestamped logs of events. Many have an on/off status change as property.

There are around 500 different types of events from multiple objects in the log.

I have tried to understand the multiple load options in qlik but am finally rather confused.

Any suggestion how to create a duration table in addition to the event table which would give me the
object, event and the event status on and off timestamps as a event duration record?

Or would you recommend to use another tool to create such a duration list externally (like awk or logparser)

Thanks

Jürg

[#2 · Accepted solution]

If you load the data ordered by event type and timestamp, you should be able to calculate the differences in the load script. I believe the code should look like

Input_raw:
LOAD
 event_type,
 tstamp
FROM
 whatever_datasource;

Input_ordered:
LOAD
 event_type,
 tstamp
RESIDENT
 Input_raw
ORDER BY
 event_type,
 tstamp;

Input_tdiff:
LOAD
 *,
 if( event_type = previous( event_type ), tstamp - previous( tstamp ), null() ) AS tdiff
RESIDENT
 Input_ordered;

DROP TABLES Input_raw, Input_ordered;

Hope this helps,

Hellmar

[#3]

Thanks Hellmar, I have tried to implement with my data and results look promising. I will try to start handling of missing and duplicate on/off events.

Jürg

[#4]

Hi,

i noticed that you should sort your time table the other way around...

Here's why:

If you would like to calculate the time an item has been in a specific status, you should:

• sort the table by item (ascending) and then by status timestamp (descending)
• if this is the most recent status entry (timestamp is highest value for this item), this item has been in this status ever since that timestamp until or now or the latest value in your timestamps or the date you made the report
• if this is not the most recent status entry for this item, simply substract the more recent timestamp with this one to get the duration

Or in code:

load

          *,

          if(item=previous(item),interval((previous(TimeStamp)-TimeStamp),'dd'),now()-TimeStamp) as StatusDurationInDays

Resident StatusTable

Order by item,TimeStamp desc;

Herbert