topic Re: Difference between timestamps, within sessions in QlikView

Difference between timestamps, within sessions

Thu, 23 Jul 2015 15:25:55 GMT

Hi everyone,

I have Audit log data with Timestamps and Users, and I have calculated a Session ID as well. Looks like this:

Session	Timestamp	User	Message
1	2015-07-15 11:19:52	local\John	Apply Server\BM1356-45
1	2015-07-15 11:20:09	local\John	Year 2014
2	2015-07-15 11:20:20	local\Mary	PasdgasdgOMM SYS FAC
1	2015-07-15 11:21:46	local\John	Year 2013
1	2015-07-15 11:21:48	local\John	Year 2014
3	2015-07-15 11:22:22	local\Joe	Activated sheet Document\SH04
3	2015-07-15 11:22:22	local\Joe	Apply Server\BM1350-45
6	2015-07-15 11:45:26	local\Joe	asdfs
6	2015-07-15 11:45:46	local\Joe	Sheet Object Document\TB04
7	2015-07-15 12:03:10	local\John	Clear All
7	2015-07-15 12:03:34	local\John	Coat 255

My question is, within Sessions, I want to calculate a new "Session Time" column that takes the difference between the current timestamp and the first timestamp in the session. So Session 1 would get:

Session Time	Session	Timestamp
0:00	1	2015-07-15 11:19:52
0:17	1	2015-07-15 11:20:09
1:54	1	2015-07-15 11:21:46
1:56	1	2015-07-15 11:21:48

Does anyone know how I could do this? Thanks!

Re: Difference between timestamps, within sessions

swuehl — Thu, 23 Jul 2015 15:59:20 GMT

This should do the session time:

Audit:

LOAD

Timestamp,

Document,

User,

Message

FROM

[LogDemoNew.txt]

(txt, utf8, embedded labels, delimiter is '\t', msq);

SessionID:

LOAD

if(peek(User)<> User or peek(Timestamp)-Timestamp > interval#('10','mm'), rangesum(1, Peek(Session)), Peek(Session) ) as Session,

Timestamp,

User,

Document,

Message

RESIDENT Audit

ORDER BY User, Timestamp desc;

DROP TABLE Audit;

SessionTime:

LOAD *, if(Peek(Session) <> Session, 0, interval(rangesum( Timestamp, -peek(Timestamp), peek(SessionTime)),'hh:mm:ss')) as SessionTime

RESIDENT SessionID

ORDER BY User,Timestamp asc;

DROP TABLE SessionID;

SessionIDResorted:

NOCONCATENATE

LOAD Autonumber(Session) as Session,

SessionTime,

Timestamp,

User,

Document,

Message

RESIDENT SessionTime

ORDER BY Timestamp;

DROP TABLE SessionTime;

Re: Difference between timestamps, within sessions

Thu, 23 Jul 2015 22:33:36 GMT

Thank you Swuehl!!!