topic difference between login and logout time in QlikView

difference between login and logout time

Thu, 30 Jun 2016 08:48:01 GMT

i have one data where user is doing multiple login and logout in day and i have to find the total number of hours user was logged in system

sample data

Name	Date	Login_Date	Time	User Name	Terminal	Message Text
kcinhjsap19	09.09.2015	09-09-2015	08:46:38	AAMRAA	l-3334	Logon Successful (Type=A)
kcinhjsap19	09.09.2015	09-09-2015	09:19:30	AAMRAA	l-3334	User Logoff
kcinhjsap19	09.09.2015	09-09-2015	11:42:13	AAMRAA	l-3334	Logon Successful (Type=A)
kcinhjsap19	09.09.2015	09-09-2015	12:54:25	AAMRAA	l-3334	User Logoff
kcinhjsap19	09.09.2015	09-09-2015	13:07:26	AAMRAA	l-3334	Logon Successful (Type=A)
kcinhjsap19	09.09.2015	09-09-2015	13:40:07	AAMRAA	l-3334	User Logoff
kcinhjsap19	10.09.2015	10-09-2015	09:06:00	AAMRAA	l-3334	Logon Successful (Type=A)
kcinhjsap19	10.09.2015	10-09-2015	09:40:33	AAMRAA	l-3334	User Logoff
kcinhjsap19	10.09.2015	10-09-2015	14:57:33	AAMRAA	l-3334	Logon Successful (Type=A)
kcinhjsap19	10.09.2015	10-09-2015	15:04:51	AAMRAA	l-3334	User Logoff
kcinhjsap19	11.09.2015	11-09-2015	10:20:45	AAMRAA	l-3334	Logon Successful (Type=A)
kcinhjsap19	11.09.2015	11-09-2015	10:47:52	AAMRAA	l-3334	User Logoff
kcinhjsap19	11.09.2015	11-09-2015	12:50:29	AAMRAA	l-3334	Logon Successful (Type=A)
kcinhjsap19	11.09.2015	11-09-2015	13:10:37	AAMRAA	l-3334	User Logoff
kcinhjsap19	11.09.2015	11-09-2015	15:32:42	AAMRAA	l-3334	Logon Successful (Type=A)
kcinhjsap19	11.09.2015	11-09-2015	15:54:24	AAMRAA	l-3334	User Logoff
kcinhjsap19	14.09.2015	14-09-2015	09:47:31	AAMRAA	l-3334	Logon Successful (Type=A)
kcinhjsap19	14.09.2015	14-09-2015	10:13:27	AAMRAA	l-3334	User Logoff
kcinhjsap19	14.09.2015	14-09-2015	12:19:45	AAMRAA	l-3334	Logon Successful (Type=A)
kcinhjsap19	14.09.2015	14-09-2015	12:40:53	AAMRAA	l-3334	User Logoff
kcinhjsap19	15.09.2015	15-09-2015	09:52:06	AAMRAA	l-3334	Logon Successful (Type=A)
kcinhjsap19	15.09.2015	15-09-2015	09:53:43	AAMRAA	l-3334	User Logoff

Re: difference between login and logout time

swuehl — Thu, 30 Jun 2016 14:09:19 GMT

You can start with something like

INPUT:

LOAD * INLINE [

Name Date Login_Date Time User Name Terminal Message Text

kcinhjsap19 09.09.2015 09-09-2015 08:46:38 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 09.09.2015 09-09-2015 09:19:30 AAMRAA l-3334 User Logoff

kcinhjsap19 09.09.2015 09-09-2015 11:42:13 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 09.09.2015 09-09-2015 12:54:25 AAMRAA l-3334 User Logoff

kcinhjsap19 09.09.2015 09-09-2015 13:07:26 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 09.09.2015 09-09-2015 13:40:07 AAMRAA l-3334 User Logoff

kcinhjsap19 10.09.2015 10-09-2015 09:06:00 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 10.09.2015 10-09-2015 09:40:33 AAMRAA l-3334 User Logoff

kcinhjsap19 10.09.2015 10-09-2015 14:57:33 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 10.09.2015 10-09-2015 15:04:51 AAMRAA l-3334 User Logoff

kcinhjsap19 11.09.2015 11-09-2015 10:20:45 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 11.09.2015 11-09-2015 10:47:52 AAMRAA l-3334 User Logoff

kcinhjsap19 11.09.2015 11-09-2015 12:50:29 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 11.09.2015 11-09-2015 13:10:37 AAMRAA l-3334 User Logoff

kcinhjsap19 11.09.2015 11-09-2015 15:32:42 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 11.09.2015 11-09-2015 15:54:24 AAMRAA l-3334 User Logoff

kcinhjsap19 14.09.2015 14-09-2015 09:47:31 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 14.09.2015 14-09-2015 10:13:27 AAMRAA l-3334 User Logoff

kcinhjsap19 14.09.2015 14-09-2015 12:19:45 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 14.09.2015 14-09-2015 12:40:53 AAMRAA l-3334 User Logoff

kcinhjsap19 15.09.2015 15-09-2015 09:52:06 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 15.09.2015 15-09-2015 09:53:43 AAMRAA l-3334 User Logoff

] (delimiter is '\t');

RESULT:

LOAD *,

If([Message Text] LIKE '*Logoff' and Previous([User Name])=[User Name],

Interval(Timestamp - Previous(Timestamp)))

as Duration;

LOAD *, Date+Time as Timestamp RESIDENT INPUT

ORDER BY [User Name], Date, Time;

DROP TABLE INPUT;

Then aggregate the new Duration field in your chart objects:

=Interval(Sum(Duration))

Re: difference between login and logout time

Fri, 01 Jul 2016 04:49:45 GMT

tanks lot swuehl‌ its working i am so sorry for not been clear i have to apply

1. If same message type are repeated consecutively then consider only 1^st line and delete the rest of the line for that user coming consecutively.

this condition as well if you can tell it will be great help

Re: difference between login and logout time

Saravanan_Desingh — Tue, 12 Jul 2016 15:48:44 GMT

I just modified swuehl‌ code. Please check if it useful.

INPUT:
LOAD * INLINE [
Name, Date, Login_Date, Time, "User Name", Terminal, "Message Text"
kcinhjsap19, 09.09.2015, 09-09-2015, 08:46:38, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 09.09.2015, 09-09-2015, 09:19:30, AAMRAA, l-3334, User Logoff
kcinhjsap19, 09.09.2015, 09-09-2015, 11:42:13, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 09.09.2015, 09-09-2015, 12:54:25, AAMRAA, l-3334, User Logoff
kcinhjsap19, 09.09.2015, 09-09-2015, 13:07:26, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 09.09.2015, 09-09-2015, 13:40:07, AAMRAA, l-3334, User Logoff
kcinhjsap19, 10.09.2015, 10-09-2015, 09:06:00, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 10.09.2015, 10-09-2015, 09:40:33, AAMRAA, l-3334, User Logoff
kcinhjsap19, 10.09.2015, 10-09-2015, 14:57:33, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 10.09.2015, 10-09-2015, 15:04:51, AAMRAA, l-3334, User Logoff
kcinhjsap19, 11.09.2015, 11-09-2015, 10:20:45, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 11.09.2015, 11-09-2015, 10:47:52, AAMRAA, l-3334, User Logoff
kcinhjsap19, 11.09.2015, 11-09-2015, 12:50:29, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 11.09.2015, 11-09-2015, 13:10:37, AAMRAA, l-3334, User Logoff
kcinhjsap19, 11.09.2015, 11-09-2015, 15:32:42, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 11.09.2015, 11-09-2015, 15:54:24, AAMRAA, l-3334, User Logoff
kcinhjsap19, 14.09.2015, 14-09-2015, 09:47:31, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 14.09.2015, 14-09-2015, 10:13:27, AAMRAA, l-3334, User Logoff
kcinhjsap19, 14.09.2015, 14-09-2015, 12:19:45, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 14.09.2015, 14-09-2015, 12:40:53, AAMRAA, l-3334, User Logoff
kcinhjsap19, 15.09.2015, 15-09-2015, 09:52:06, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 15.09.2015, 15-09-2015, 09:53:43, AAMRAA, l-3334, User Logoff
] (delimiter is ',');

RESULT:
//LOAD *,
//     If([Message Text] LIKE '*Logoff' and Previous([User Name])=[User Name],
//               Interval(Timestamp - Previous(Timestamp)))
//                    as Duration;
Load *,
  Interval(OutTime - InTime) As Duration;
Load
  FirstSortedValue([User Name],Time) As InUser,
  FirstSortedValue(Time,Time) As InTime,
  FirstSortedValue(Time,-Time) As OutTime,
  Date
Group By [User Name], Date
  ;
LOAD *, Date#(Date,'DD.MM.YYYY')+Time as Timestamp RESIDENT INPUT
ORDER BY [User Name], Date, Time;


DROP TABLE INPUT;

Re: difference between login and logout time

Wed, 13 Jul 2016 12:58:40 GMT

Thanks lot. for your help