6 Replies Latest reply: Nov 23, 2012 3:12 AM by Sauli Masala RSS

    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

        • Re: Counting time with several changing values
          Gysbert Wassenaar

          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

              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

                • Re: Counting time with several changing values
                  Gysbert Wassenaar

                  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

                      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

                        • Re: Counting time with several changing values

                          Hey,

                           

                          Anyone got any solutions for this problem ?

                           

                          -Matt

                            • Re: Counting time with several changing values
                              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