Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone, quite in need of some help
[Usually I scout forums to find answers but this time couldn't]
I'm trying to calculate time spent on each page of a document based on server logs and basically got files with the following structure:
User | Time | Type | Sheet | Object | Session |
---|---|---|---|---|---|
Tony | 10:40 | Open | SH10 | Document | 1 |
Tony | 10:50 | Activated Sheet | SH12 | Sheet | 1 |
Tony | 11:00 | Activated Sheet | SH15 | Sheet | 1 |
Tony | 11:20 | Activated Sheet | SH12 | Sheet | 1 |
Tony | 11:30 | Activated Sheet | SH10 | Sheet | 1 |
Tony | 11:50 | Close | SH10 | Document | 1 |
As an outcome I'm trying to get a table that looks like that:
User | Session | Sheet | TimeSpent |
---|---|---|---|
Tony | 1 | SH10 | 00:30 |
Tony | 1 | SH12 | 00:20 |
Tony | 1 | SH15 | 00:20 |
I get that I need to go from the first record to second (and so on), count the difference between timestamps and attribute that difference to the first Sheet. But struggling to figure out how to wrap it in QV code.
Any help would be really appreciated
This?
Script:
Table:
LOAD User,
Time(Time#(Time, 'hh:mm'), 'hh:mm') as Time,
Type,
Sheet,
Object,
Session
FROM
[https://community.qlik.com/thread/230379]
(html, codepage is 1252, embedded labels, table is @1);
FinalTable:
LOAD *,
Interval(If(User = Previous(User), Previous(Time) - Time, 0), 'hh:mm') as TimeSpent
Resident Table
Order By Time desc;
DROP Table Table;
This?
Script:
Table:
LOAD User,
Time(Time#(Time, 'hh:mm'), 'hh:mm') as Time,
Type,
Sheet,
Object,
Session
FROM
[https://community.qlik.com/thread/230379]
(html, codepage is 1252, embedded labels, table is @1);
FinalTable:
LOAD *,
Interval(If(User = Previous(User), Previous(Time) - Time, 0), 'hh:mm') as TimeSpent
Resident Table
Order By Time desc;
DROP Table Table;
Thank you, that's just what i've needed