Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to analyze logfile and extract from logfile start and end time for each working session
event | time |
---|---|
start | 10:00 |
do thing1 | 10:04 |
do thing2 | 10:13 |
end | 11:00 |
start | 11:11 |
do thing 1 | 11:20 |
end | 11:45 |
In need to get table
id | start time | end time |
---|---|---|
1 | 10:00 | 11:00 |
2 | 11:11 | 11:45 |
Message was edited by: Dwarf Dwarfsorg
Perhaps some changes are needed, but generally would an approach like this work:
t1:
Load
event, time,
if(event = 'start', time) as starttime,
if(event = 'end', time) as endtime,
if(rowno() = 1, 1, if(event <> 'start', peek('id'), peek('id') + 1)) as id
From xyz;
t2:
Load id, starttime, endtime Resident t1 Where starttime > 0;
drop tables t1;
- Marcus
Perhaps some changes are needed, but generally would an approach like this work:
t1:
Load
event, time,
if(event = 'start', time) as starttime,
if(event = 'end', time) as endtime,
if(rowno() = 1, 1, if(event <> 'start', peek('id'), peek('id') + 1)) as id
From xyz;
t2:
Load id, starttime, endtime Resident t1 Where starttime > 0;
drop tables t1;
- Marcus
The solution is working perfect for one user .The real log has also users
How can i improve solution to deal with users
user | event | time |
---|---|---|
jack | Start | 10:00 |
Kevin | Start | 10:01 |
jack | Do thing1 | 10:12 |
Keving | Do thing 1 | 10:12 |
jack | End | 11:01 |
Kevin | End | 11.01 |
jack | Start | 11:02 |
jack | End | 12:00 |
i need table
user | id | start | end |
---|---|---|---|
kevin | 1 | 10:01 | 11:01 |
jack | 1 | 10:00 | 11:01 |
kevin | 2 | 11:02 | 12:00 |
You could use a for loop, so that you loop through each user in turn and run that peek code
For this you need a properly sorted table - therefore one step more, try this:
t0:
Load event, time, user From xyz;
t1:
Load
event, time, user,
if(event = 'start', time) as starttime,
if(event = 'end', time) as endtime,
if(rowno() = 1 or user <> peek('user'), 1, if(event <> 'start', peek('id'), peek('id') + 1)) as id
Resident t0 order by user, time;
t2:
Load user id, starttime, endtime Resident t1 Where starttime > 0;
drop tables t0, t1;
- Marcus
Bingo !