Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Difference between timestamps, within sessions

Hi everyone,

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

    

SessionTimestampUserMessage
12015-07-15 11:19:52local\JohnApply Server\BM1356-45
12015-07-15 11:20:09local\JohnYear 2014
22015-07-15 11:20:20local\MaryPasdgasdgOMM SYS FAC
12015-07-15 11:21:46local\JohnYear 2013
12015-07-15 11:21:48local\JohnYear 2014
32015-07-15 11:22:22local\JoeActivated sheet Document\SH04
32015-07-15 11:22:22local\JoeApply Server\BM1350-45
62015-07-15 11:45:26local\Joeasdfs
62015-07-15 11:45:46local\JoeSheet Object Document\TB04
72015-07-15 12:03:10local\JohnClear All
72015-07-15 12:03:34local\JohnCoat 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 TimeSessionTimestamp
0:0012015-07-15 11:19:52
0:1712015-07-15 11:20:09
1:5412015-07-15 11:21:46
1:5612015-07-15 11:21:48

Does anyone know how I could do this? Thanks!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

2 Replies
swuehl
MVP
MVP

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;

Not applicable
Author

Thank you Swuehl!!!