topic Re: Counting time with several changing values in QlikView

Counting time with several changing values

Sat, 10 Nov 2012 10:21:59 GMT

Hello!

I'm kind of new with qlikview and struggling with a problem concerning counting time.

So here is the problem:

Date	ObjectId	Date (Unix timestamp)	State
120812	33	1345064401	4
120812	33	etc..	5
120812	33	etc..	6
120812	33	etc..	5
120812	33	etc..	6
120812	33	etc..	5
120812	33	etc..	6
120812	33	etc..	3
120812	34	etc..	4
120812	34	etc..	5
120812	34	etc..	6
120812	34	etc..	5
120812	34	etc..	6
120812	34	etc..	3

States meaning 4 = Logging in a program, 5= Temporarily logging out of the program, 6= Logging in again from temporary logout. 3 = Logging out.

1. I should be able to count certain Objects time difference between state 4 and 3. In a certain date. The tricky part is that it is possible that State 3 is missing from certain Object on a certain date, then i should be able to stop the counting on clock 0.00.

2. Then i should be able to calculate the time difference between 5 and 6 in a day. Counting the time on temporarily logged out state.

Hopefully someone has some ideas how this could be counted, really appciate it !

Matt

Re: Counting time with several changing values

Gysbert_Wassenaar — Sat, 10 Nov 2012 14:15:03 GMT

See attached qvw. It calculates session times, ending a session on a date change. It doesn't do this for temporary logouts/logins. Is that what you need?

Re: Counting time with several changing values

Sun, 11 Nov 2012 10:59:33 GMT

Hey Gysbert,

Tested the example you gave me and it works pretty well!

Only problem I have right now is some weird values on LastLogin column.

Im getting following values in the addition of the right values:

EN_OBJECTID	Date	Time	EN_STATE	LastLogin	SessionTime	TempLogoutTime
726	26.9.2012	7:51:26	5	30.1.2231 0:00:00
726	27.9.2012	0:00:02	3	31.1.2231 0:00:00	1913968:08:34
726	28.9.2012	0:00:01	3	1.2.2231 0:00:00	1913968:09:06
726	29.9.2012	0:00:01	3	2.2.2231 0:00:00	1913968:08:44
726	30.9.2012	0:00:02	3	3.2.2231 0:00:00	24:00:00
726	1.10.2012	0:00:01	3	15.4.2231 0:00:00	1704:00:00
726	2.10.2012	0:00:02	3	16.4.2231 0:00:00	1915648:06:15
726	3.10.2012	0:00:02	3	17.4.2231 0:00:00	1915648:08:30
726	3.10.2012	7:55:49	5	17.4.2231 0:00:00
726	4.10.2012	0:00:02	3	18.4.2231 0:00:00	1915648:04:11
726	5.10.2012	0:00:01	3	19.4.2231 0:00:00	24:00:00
726	5.10.2012	7:54:51	5	19.4.2231 0:00:00
726	6.10.2012	0:00:01	3	20.4.2231 0:00:00	1915648:05:09

Im now using following load:

Event:
LOAD
DateTicket,
EN_OBJECTID,
timestamp(ConvertToLocalTime(Date(MakeDate(1970, 1, 1) + ("EN_DATE" / 86400)), 'GMT+02:00')) as TS,
date(floor(ConvertToLocalTime(Date(MakeDate(1970, 1, 1) + ("EN_DATE" / 86400)), 'GMT+02:00'))) as Date,
time(frac(ConvertToLocalTime(Date(MakeDate(1970, 1, 1) + ("EN_DATE" / 86400)), 'GMT+02:00'))) as Time,
EN_STATE
FROM
Event.qvd
(qvd);

Foo:
load *,
if(rowno()=1 and EN_STATE=4,TS,if(EN_STATE=4,TS, if(DateTicket<>peek(DateTicket),timestamp(floor(DateTicket)), peek(LastLogin)))) as LastLogin,
if(rowno()=1 and EN_STATE=5,TS,if(EN_STATE=5,TS,peek(LastTempLogout))) as LastTempLogout,
if(rowno()>1,
if(DateTicket<>peek(DateTicket) and EN_OBJECTID=peek(EN_OBJECTID),
interval(floor(DateTicket)-peek(LastLogin),'hh:mm:ss'),
if(EN_STATE=3,
interval(TS-peek(LastLogin),'hh:mm:ss')))) as SessionTime,
if(rowno()>1,if(EN_STATE=6,interval(TS-peek(LastTempLogout),'hh:mm:ss'))) as TempLogoutTime
Resident Event order by EN_OBJECTID, TS;

drop Table Event;

Also trying to figure out how to display this on Bar chart correctly.

Really appriciate your help!

Matt

Re: Counting time with several changing values

Gysbert_Wassenaar — Sun, 11 Nov 2012 11:14:58 GMT

You're using DateTicket instead of the field Date. I noticed the Date field in your table in your first post didn't have a complete 4-digit year. And that the date didn't match the date calculated from the unix timestamp. Possibly the missing year digits is what is causing your strange LastLogin datetimes. I think you're better off using the date calculated from the unix timestamp.

Re: Counting time with several changing values

Wed, 14 Nov 2012 10:04:18 GMT

Hey thanks for the tip! It works much better with using the timestamp instead of Dateticket.

One thing I didnt consider right away was that. It seems that I get EN_STATE=3 every day at midnight meaning a logoff.

Then at first login EN_STATE=4 meaning login after that comes always EN_STATE=5. This means that i would need the interval between states 6 and 5 In addition I got EN_INFO telling me the reason for temporary logout.

Date	DateTicket	EN_DATE	EN_INFO	EN_OBJECTID	EN_STATE	Time	TS
6.9.2012	120906	1346878801	0	876	3	0:00:01	6.9.2012 0:00:01
6.9.2012	120906	1346909074	0	876	4	8:24:34	6.9.2012 8:24:34
6.9.2012	120906	1346909074	0	876	5	8:24:34	6.9.2012 8:24:34
6.9.2012	120906	1346910493	1	876	6	8:48:13	6.9.2012 8:48:13
6.9.2012	120906	1346910688	0	876	5	8:51:28	6.9.2012 8:51:28
6.9.2012	120906	1346910779	0	876	3	8:52:59	6.9.2012 8:52:59
6.9.2012	120906	1346920495	0	876	4	11:34:55	6.9.2012 11:34:55
6.9.2012	120906	1346920495	0	876	5	11:34:55	6.9.2012 11:34:55
6.9.2012	120906	1346921157	1	876	6	11:45:57	6.9.2012 11:45:57
6.9.2012	120906	1346921492	0	876	5	11:51:32	6.9.2012 11:51:32
6.9.2012	120906	1346921702	1	876	6	11:55:02	6.9.2012 11:55:02
6.9.2012	120906	1346922050	0	876	5	12:00:50	6.9.2012 12:00:50
6.9.2012	120906	1346922627	1	876	6	12:10:27	6.9.2012 12:10:27
6.9.2012	120906	1346923147	0	876	5	12:19:07	6.9.2012 12:19:07
6.9.2012	120906	1346923537	1	876	6	12:25:37	6.9.2012 12:25:37
6.9.2012	120906	1346925738	0	876	5	13:02:18	6.9.2012 13:02:18
6.9.2012	120906	1346925891	0	876	3	13:04:51	6.9.2012 13:04:51
6.9.2012	120906	1346934053	0	876	4	15:20:53	6.9.2012 15:20:53
6.9.2012	120906	1346934053	0	876	5	15:20:53	6.9.2012 15:20:53
6.9.2012	120906	1346934172	0	876	3	15:22:52	6.9.2012 15:22:52

How is it possible to take into account that I always get combination of 4 and 5 at login. And then there can be these combinations in a same day. At same time counting the duration of State 6 and comparing that to the next State 5 value.

Matt

Re: Counting time with several changing values

Tue, 20 Nov 2012 14:02:43 GMT

Hey,

Anyone got any solutions for this problem ?

-Matt

Re: Counting time with several changing values

Fri, 23 Nov 2012 08:12:43 GMT

Date	DateTicket	EN_DATE	EN_INFO	EN_OBJECTID	EN_STATE	Time	TS	Outcome
6.9.2012	120906	1346878801	0	876	3	0:00:01	6.9.2012 0:00	0:00:00
6.9.2012	120906	1346909074	0	876	4	8:24:34	6.9.2012 8:24	0:00:00
6.9.2012	120906	1346909074	0	876	5	8:24:34	6.9.2012 8:24	0:00:00
6.9.2012	120906	1346910493	1	876	6	8:48:13	6.9.2012 8:48	0:03:15
6.9.2012	120906	1346910688	0	876	5	8:51:28	6.9.2012 8:51	0:00:00
6.9.2012	120906	1346910779	0	876	3	8:52:59	6.9.2012 8:52	0:28:25
6.9.2012	120906	1346920495	0	876	4	11:34:55	6.9.2012 11:34	0:00:00
6.9.2012	120906	1346920495	0	876	5	11:34:55	6.9.2012 11:34	0:00:00
6.9.2012	120906	1346921157	1	876	6	11:45:57	6.9.2012 11:45	0:05:35
6.9.2012	120906	1346921492	0	876	5	11:51:32	6.9.2012 11:51	0:00:00
6.9.2012	120906	1346921702	1	876	6	11:55:02	6.9.2012 11:55	0:05:48
6.9.2012	120906	1346922050	0	876	5	12:00:50	6.9.2012 12:00	0:00:00
6.9.2012	120906	1346922627	1	876	6	12:10:27	6.9.2012 12:10	0:08:40
6.9.2012	120906	1346923147	0	876	5	12:19:07	6.9.2012 12:19	0:00:00
6.9.2012	120906	1346923537	1	876	6	12:25:37	6.9.2012 12:25	0:36:41
6.9.2012	120906	1346925738	0	876	5	13:02:18	6.9.2012 13:02	0:00:00
6.9.2012	120906	1346925891	0	876	3	13:04:51	6.9.2012 13:04	1:29:56
6.9.2012	120906	1346934053	0	876	4	15:20:53	6.9.2012 15:20	0:00:00
6.9.2012	120906	1346934053	0	876	5	15:20:53	6.9.2012 15:20	0:00:00
6.9.2012	120906	1346934172	0	876	3	15:22	0:01:07

Haven't been able to solve this one. Here is example of the hoped outcome.

Need to know the difference between EN_STATES 3 and 4. And this would be calculated on EN_STATE=3 row.

At the beginning i get one EN_STATE=3, which would be ignored. And after EN_STATE=4 I get one EN_STATE=5 which doesnt connect to EN_STATE=6, so it would be ignored.

Third i Would need the the difference between EN_STATE=6 and next EN_STATE=5, and this interval would be dispalyed on the EN_STATE=6 row.

Hoping someone has ideas for this one

-Matt