Calculate duration over multiple rows with specific values

[#1]

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!

[#2]

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.

[Gysbert_Wassenaar]

See attached example

[#4]

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;

[MarcoWedel]

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