Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
dwarfsorg
Contributor II
Contributor II

analyzing logfiles

I need to analyze logfile and extract from logfile start and end  time for each working session

event

time
start10:00
do thing110:04
do thing2 10:13
end 11:00
start11:11
do thing 111:20
end 11:45

In need to get table

idstart timeend time
110:0011:00
211:1111:45

Message was edited by: Dwarf Dwarfsorg

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

5 Replies
marcus_sommer

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
Contributor II
Contributor II
Author

The solution  is working perfect for one user  .The real log has  also users

How can i improve solution to deal with users

usereventtime
jackStart10:00
KevinStart10:01
jackDo thing110:12
KevingDo thing 110:12
jackEnd11:01
KevinEnd11.01
jackStart11:02
jackEnd12:00

i need table

useridstartend
kevin110:0111:01
jack110:0011:01
kevin211:0212:00
Not applicable

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
Contributor II
Contributor II
Author

Bingo !