analyzing logfiles

[dwarfsorg]

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

[marcus_sommer · Accepted solution]

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

[dwarfsorg]

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

[#4]

You could use a for loop, so that you loop through each user in turn and run that peek code

[marcus_sommer]

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

[dwarfsorg]

Bingo !