Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2012-11-10 05:21 AM

Counting time with several changing values

Hello!

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

So here is the problem:

DateObjectIdDate (Unix timestamp)State
1208123313450644014
12081233etc..5
12081233etc..6
12081233etc..5
12081233etc..6
12081233etc..5
12081233etc..6
12081233etc..3
12081234etc..4
12081234etc..5
12081234etc..6
12081234etc..5
12081234etc..6
12081234etc..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

701 Views
0 Likes
6 Replies
Gysbert_Wassenaar
MVP
MVP
‎2012-11-10 09:15 AM

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?


talk is cheap, supply exceeds demand
comm65971.qvw
383 Views
Not applicable
‎2012-11-11 05:59 AM
Author

In response to Gysbert_Wassenaar

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_OBJECTIDDateTimeEN_STATELastLoginSessionTimeTempLogoutTime
72626.9.20127:51:26530.1.2231 0:00:00
72627.9.20120:00:02331.1.2231 0:00:001913968:08:34
72628.9.20120:00:0131.2.2231 0:00:001913968:09:06
72629.9.20120:00:0132.2.2231 0:00:001913968:08:44
72630.9.20120:00:0233.2.2231 0:00:0024:00:00
7261.10.20120:00:01315.4.2231 0:00:001704:00:00
7262.10.20120:00:02316.4.2231 0:00:001915648:06:15
7263.10.20120:00:02317.4.2231 0:00:001915648:08:30
7263.10.20127:55:49517.4.2231 0:00:00
7264.10.20120:00:02318.4.2231 0:00:001915648:04:11
7265.10.20120:00:01319.4.2231 0:00:0024:00:00
7265.10.20127:54:51519.4.2231 0:00:00
7266.10.20120:00:01320.4.2231 0:00:001915648: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

383 Views
0 Likes
Gysbert_Wassenaar
MVP
MVP
‎2012-11-11 06:14 AM

In response to

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.


talk is cheap, supply exceeds demand
383 Views
0 Likes
Not applicable
‎2012-11-14 05:04 AM
Author

In response to Gysbert_Wassenaar

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.

DateDateTicketEN_DATEEN_INFOEN_OBJECTIDEN_STATETimeTS
6.9.20121209061346878801087630:00:016.9.2012 0:00:01
6.9.20121209061346909074087648:24:346.9.2012 8:24:34
6.9.20121209061346909074087658:24:346.9.2012 8:24:34
6.9.20121209061346910493187668:48:136.9.2012 8:48:13
6.9.20121209061346910688087658:51:286.9.2012 8:51:28
6.9.20121209061346910779087638:52:596.9.2012 8:52:59
6.9.201212090613469204950876411:34:556.9.2012 11:34:55
6.9.201212090613469204950876511:34:556.9.2012 11:34:55
6.9.201212090613469211571876611:45:576.9.2012 11:45:57
6.9.201212090613469214920876511:51:326.9.2012 11:51:32
6.9.201212090613469217021876611:55:026.9.2012 11:55:02
6.9.201212090613469220500876512:00:506.9.2012 12:00:50
6.9.201212090613469226271876612:10:276.9.2012 12:10:27
6.9.201212090613469231470876512:19:076.9.2012 12:19:07
6.9.201212090613469235371876612:25:376.9.2012 12:25:37
6.9.201212090613469257380876513:02:186.9.2012 13:02:18
6.9.201212090613469258910876313:04:516.9.2012 13:04:51
6.9.201212090613469340530876415:20:536.9.2012 15:20:53
6.9.201212090613469340530876515:20:536.9.2012 15:20:53
6.9.201212090613469341720876315:22:526.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

383 Views
0 Likes
Not applicable
‎2012-11-20 09:02 AM
Author

In response to

Hey,

Anyone got any solutions for this problem ?

-Matt

383 Views
0 Likes
Not applicable
‎2012-11-23 03:12 AM
Author

In response to
DateDateTicketEN_DATEEN_INFOEN_OBJECTIDEN_STATETimeTSOutcome
6.9.20121209061346878801087630:00:016.9.2012 0:000:00:00
6.9.20121209061346909074087648:24:346.9.2012 8:240:00:00
6.9.20121209061346909074087658:24:346.9.2012 8:240:00:00
6.9.20121209061346910493187668:48:136.9.2012 8:480:03:15
6.9.20121209061346910688087658:51:286.9.2012 8:510:00:00
6.9.20121209061346910779087638:52:596.9.2012 8:520:28:25
6.9.201212090613469204950876411:34:556.9.2012 11:340:00:00
6.9.201212090613469204950876511:34:556.9.2012 11:340:00:00
6.9.201212090613469211571876611:45:576.9.2012 11:450:05:35
6.9.201212090613469214920876511:51:326.9.2012 11:510:00:00
6.9.201212090613469217021876611:55:026.9.2012 11:550:05:48
6.9.201212090613469220500876512:00:506.9.2012 12:000:00:00
6.9.201212090613469226271876612:10:276.9.2012 12:100:08:40
6.9.201212090613469231470876512:19:076.9.2012 12:190:00:00
6.9.201212090613469235371876612:25:376.9.2012 12:250:36:41
6.9.201212090613469257380876513:02:186.9.2012 13:020:00:00
6.9.201212090613469258910876313:04:516.9.2012 13:041:29:56
6.9.201212090613469340530876415:20:536.9.2012 15:200:00:00
6.9.201212090613469340530876515:20:536.9.2012 15:200:00:00
6.9.201212090613469341720876315:220: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

383 Views
0 Likes