Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
See attached example
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;
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