topic Re: Create session ID from data with timestamp in QlikView

Create session ID from data with timestamp

Thu, 23 Jul 2015 13:09:44 GMT

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:

Timestamp	Document	User	Message
7/15/2015 11.19:52	Analysis.qvw	John	Selected XYZ
7/15/2015 11.21:46	Analysis.qvw	John	Activated Sheet
7/15/2015 11.21:52	Analysis.qvw	John	Selected ABC
7/15/2015 11.22:13	Budget.qvw	John	Export to Excel
7/15/2015 11.22:30	Budget.qvw	John	asdasd
7/15/2015 11.22:14	Analysis.qvw	Mary	yj
7/15/2015 11.22:25	Analysis.qvw	Mary	ty
7/15/2015 1.15:05	Analysis.qvw	John	adfh
7/15/2015 1.17:10	Analysis.qvw	John	adfaer

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?

Re: Create session ID from data with timestamp

swuehl — Thu, 23 Jul 2015 13:20:45 GMT

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;

Re: Create session ID from data with timestamp

marcus_sommer — Thu, 23 Jul 2015 13:22:05 GMT

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

Re: Create session ID from data with timestamp

Thu, 23 Jul 2015 13:46:23 GMT

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

SessionID	Timestamp	User	Message
	7/15/2015 11.19:52	John	Selected XYZ
	7/15/2015 11.21:46	John	Activated Sheet
	7/15/2015 11.21:52	John	Selected ABC
	7/15/2015 11.22:13	John	Export to Excel
1	7/15/2015 11.22:30	John	asdasd
	7/15/2015 11.22:14	Mary	yj
1	7/15/2015 11.22:25	Mary	ty
	7/15/2015 1.15:05	John	adfh
	7/15/2015 1.15:10	John	adfaer

Re: Create session ID from data with timestamp

Thu, 23 Jul 2015 13:48:15 GMT

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

Re: Create session ID from data with timestamp

swuehl — Thu, 23 Jul 2015 13:57:20 GMT

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?

Re: Create session ID from data with timestamp

marcus_sommer — Thu, 23 Jul 2015 13:58:55 GMT

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

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

- Marcus

Re: Create session ID from data with timestamp

Thu, 23 Jul 2015 14:03:19 GMT

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:

Session	Timestamp	User	Message
1	7/15/2015 11.19:52	John	Selected XYZ
1	7/15/2015 11.21:46	John	Activated Sheet
1	7/15/2015 11.21:52	John	Selected ABC
1	7/15/2015 11.22:13	John	Export to Excel
1	7/15/2015 11.22:30	John	asdasd
2	7/15/2015 11.22:14	Mary	yj
2	7/15/2015 11.22:25	Mary	ty
1	7/15/2015 1.15:05	John	adfh
1	7/15/2015 1.15:10	John	adfaer

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,

..."

Re: Create session ID from data with timestamp

swuehl — Thu, 23 Jul 2015 14:16:50 GMT

I believe your timestamp format is not parsed in correctly.

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

Re: Create session ID from data with timestamp

Thu, 23 Jul 2015 14:41:07 GMT

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!

Re: Create session ID from data with timestamp

swuehl — Thu, 23 Jul 2015 14:45:27 GMT

Timestamp format is OK, but it's

ORDER BY User, Timestamp desc;

edit:

Attached the sample, now also with a resorted SessionID.

Re: Create session ID from data with timestamp

Thu, 23 Jul 2015 15:05:24 GMT

This is SO COOL! Thank you Swuehl!

Re: Create session ID from data with timestamp

Thu, 23 Jul 2015 15:26:25 GMT

Swuehl,

I've posted a new but related question here, if you are interested: Difference between timestamps, within sessions

Re: Create session ID from data with timestamp

Thu, 23 Jul 2015 15:26:47 GMT

Thanks for your help Marcus. I did not know about the Class function, I will keep it in mind for other problems

Re: Create session ID from data with timestamp

Tue, 28 Jul 2015 07:28:24 GMT

Hi Swuehl

rangesum(1, Peek(Session)), could you please explain us what it will do.

here 1 means all? or from the top of the row?

Re: Create session ID from data with timestamp

swuehl — Tue, 28 Jul 2015 19:32:45 GMT

rangesum(expr1 [ , expr2, ... exprN ])

Returns the sum of a range of 1 to N arguments. As opposed to the + operator, rangesum will treat all non-numeric values as 0.

Examples:

rangesum (1,2,4) returns 7

rangesum (1,'xyz') returns 1

rangesum (null( )) returns 0