Sum of Time Intervals for Multiple Events

[#1]

I'm very new to QlickView and trying (using the Personal Edition) to sum the total outage time for multiple network devices from the following table extract:

Event Time	Location	Event
01/09/2011 15:07	ABERDEEN	Node Down
01/09/2011 15:13	ABERDEEN	Node Up
02/09/2011 08:12	ABBEY_WO	Node Down
02/09/2011 08:27	ABBEY_WO	Node Up
02/09/2011 10:13	ABBEY_WO	Node Down
02/09/2011 10:44	ABBEY_WO	Node Up
09/09/2011 08:09	ABERDEEN	Node Down
09/09/2011 08:48	ABERDEEN	Node Up

I want to calculate the time interval between each matching Node Down -> Node Up event pair and sum for each location to produce the following output:

Location	Total Outage Duration
ABERDEEN	00:45:19
ABBEY_WO	00:45:07

Can anyone suggest how this can be achieved?

Thanks in advance,

Rich.

[#2 · Accepted solution]

Hi Rich,

try if this is correct for you.

if you have problem to open file cause license this is the script:

tab:

LOAD [Event Time],

     Location,

     Event

FROM

.\Cartel1.xls

(biff, embedded labels, table is Foglio1$);

tab1:

load *,if (Peek(Location,-1)=Location and Peek(Event,-1)='Node Down',

          interval([Event Time]-peek([Event Time],-1)),0) as Outage

Resident tab

order by Location,[Event Time],Event

;

DROP Table tab;

I've made a pivot with

Dimension: Location

Expression: time(sum(Outage))

Stefano

[#3]

It works perfectly ste.san. Much appreciated. Rich