topic Re: Calculate duration over multiple rows with specific values in QlikView

Calculate duration over multiple rows with specific values

Mon, 26 May 2014 12:14:44 GMT

I have a table with multiple actions. Each action has an action type.

Participant	Event	User	Action_Type	Date/Time
1557	332	1	1	2014-05-26 13:30:12.000
1557	332	1	2	2014-05-26 13:31:00.000
1358	651	1	1	2014-05-26 13:31:33.000
1557	332	1	3	2014-05-26 13:32:00.000
1685	332	3	1	2014-05-26 13:34:00.000
1557	332	1	4	2014-05-26 13:36:00.000
1358	651	1	2	2014-05-26 13:38:00.000
1557	332	1	5	2014-05-26 13:39:00.000

I want to calculate the duration from each event per participant per event per user. the start is Action_Type 2 and the end is Action_Type 4

So with this table i want to know that the duration of event "332" from participant "1557" and User "1" is 13:36:00.000 - 13:31:00.000 = 0:05:00.000

How can i do that? Thanks for any help you can give!

Re: Calculate duration over multiple rows with specific values

Mon, 26 May 2014 12:17:56 GMT

The thing i want to do with the result is showing a chart with all the events under 15 min and all events under 30 min and so on.

Re: Calculate duration over multiple rows with specific values

Gysbert_Wassenaar — Mon, 26 May 2014 12:37:49 GMT

See attached example

Re: Calculate duration over multiple rows with specific values

Mon, 26 May 2014 17:47:08 GMT

Hi.

Try use this:

Events:

LOAD * INLINE [

Participant, Event, User, Action_Type, Date/Time

1557,332,1,1,2014-05-26 13:30:12.000

1358,651,1,1,2014-05-26 13:31:33.000

1685,332,3,1,2014-05-26 13:34:00.000

1557,332,1,2,2014-05-26 13:31:00.000

1358,651,1,2,2014-05-26 13:38:00.000

1557,332,1,3,2014-05-26 13:32:00.000

1557,332,1,4,2014-05-26 13:36:00.000

1557,332,1,5,2014-05-26 13:39:00.000

];

tempComplitedEvents:

LOAD Participant,

Event,

User,

[Date/Time] as Start

Resident Events Where Action_Type=2;

Inner Join

LOAD Participant,

Event,

User,

[Date/Time] as End

Resident Events Where Action_Type=4;

DROP Table Events;

ComplitedEvents:

LOAD Participant,

Event,

User,

Time(End -Start) as Duration

Resident tempComplitedEvents;

DROP Table tempComplitedEvents;

Re: Calculate duration over multiple rows with specific values

MarcoWedel — Tue, 27 May 2014 10:37:59 GMT

Hi Tom,

another one:

=Dual(Interval(SubField(Class(Duration, '00:15:00'),'<=', 1),'hh:mm:ss')&' - '&Interval(SubField(Class(Duration, '00:15:00'),'<', 3),'hh:mm:ss'), Duration)

tabEvents:
LOAD
  AutoNumberHash128(Participant, Event, User) as %EventID,
  *
FROM [http://community.qlik.com/thread/119762] (html, codepage is 1252, embedded labels, table is @1);

tabDateTimes:
Generic LOAD
  %EventID,
  'DateTimeActTyp'&Action_Type,
  [Date/Time]
Resident tabEvents;

hope this helps

regards

Marco