Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Create session ID from data with timestamp

Hi everyone,

I'm working with the server Audit data, which shows user activity in my QV dashboards. Each action has its own row and a timestamp. It looks something like this:

    

TimestampDocumentUserMessage
7/15/2015 11.19:52Analysis.qvwJohnSelected XYZ
7/15/2015 11.21:46Analysis.qvwJohnActivated Sheet
7/15/2015 11.21:52Analysis.qvwJohnSelected ABC
7/15/2015 11.22:13Budget.qvwJohnExport to Excel
7/15/2015 11.22:30Budget.qvwJohnasdasd
7/15/2015 11.22:14Analysis.qvwMaryyj
7/15/2015 11.22:25Analysis.qvwMaryty
7/15/2015 1.15:05Analysis.qvwJohnadfh
7/15/2015 1.17:10Analysis.qvwJohnadfaer

In the script, I want to use Timestamp and User to create a Session ID.

So John from 11.19:52 - 11.22:30 would be Session ID 1, (ignore him switching dashboards during session)

Mary is 11.22:14-11.22:25, 2

and then when John comes back later from 1.15:05-1.17:10 would be Session 3.

I think this can be done with a condition that says after 10 minutes from last user timestamp, it counts as a new session, so create a new session ID. But I don't know how to write this. Can anyone help me?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Timestamp format is OK, but it's

ORDER BY User, Timestamp desc;

edit:

Attached the sample, now also with a resorted SessionID.

View solution in original post

15 Replies
swuehl
MVP
MVP

Try

Set TimestampFormat = 'M/DD/YYYY hh.mm:ss';

INPUT:

LOAD Timestamp,

    Document,

    User,

    Message

FROM

[https://community.qlik.com/thread/173771]

(html, codepage is 1252, embedded labels, table is @1);

RESULT:

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

RESIDENT INPUT

ORDER BY User, Timestamp desc;

DROP TABLE INPUT;

marcus_sommer

Maybe this way worked or showed at least the direction:

Audit:

Load Timestamp, Document, User, Message From xyz;

     left join

Load

     Timestamp, User,

     autonumber(min(Timestamp) & ' - ' & max(Timestamp) & ' - ' User) as SessionID

Resident Audit Group by Timestamp, User;

- Marcus

Not applicable
Author

Hmm Swuehl this looked great but unfortunately gave me something like the following:

    

SessionIDTimestampUserMessage
7/15/2015 11.19:52JohnSelected XYZ
7/15/2015 11.21:46JohnActivated Sheet
7/15/2015 11.21:52JohnSelected ABC
7/15/2015 11.22:13JohnExport to Excel
17/15/2015 11.22:30Johnasdasd
7/15/2015 11.22:14Maryyj
17/15/2015 11.22:25Maryty
7/15/2015 1.15:05Johnadfh
7/15/2015 1.15:10Johnadfaer
Not applicable
Author

Marcus,

Problem with this method is that it does not take into account that I want a new SessionID if more than 10 minutes interval from last user timestamp

swuehl
MVP
MVP

How does you script look like? If you renamed Session to SessionID, you also need to rename the fields in the Peek() function...

Take care that the timestamp format code matches the format of timestamps of your input file.

If you run my exact script, using your sample data, it should show Session ids on all lines, don't you see?

marcus_sommer

Maybe you could include something like the following to get the interval:

num(class(Timestamp, 1 / 24 / 6)

- Marcus

Not applicable
Author

Sorry, I changed TimestampFormat to input and made sure everything is Session. Now I get ID on every row.

However, the 10 minute interval rule does not seem to be working:

    

SessionTimestampUserMessage
17/15/2015 11.19:52JohnSelected XYZ
17/15/2015 11.21:46JohnActivated Sheet
17/15/2015 11.21:52JohnSelected ABC
17/15/2015 11.22:13JohnExport to Excel
17/15/2015 11.22:30Johnasdasd
27/15/2015 11.22:14Maryyj
27/15/2015 11.22:25Maryty
17/15/2015 1.15:05Johnadfh
17/15/2015 1.15:10Johnadfaer

The last two rows should have ID 3.

I copied your script for it:

"

....

LOAD

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

Timestamp,

..."

swuehl
MVP
MVP

I believe your timestamp format is not parsed in correctly.

What is your input (default) format set in the script or OS?

Not applicable
Author

I think you are right, I am messing up format somewhere.

Actually, my input is a text file. To prevent confusion, I am posting a sample QVW, with real data that I need to use. I just cleaned some sensitive material.

I think I have fixed all the formats, but I am still not getting the correct use of interval#.

Thank you in advance for looking at this!