topic Re: Re: analyzing logfiles in QlikView

analyzing logfiles

dwarfsorg — Wed, 30 Jul 2014 08:50:16 GMT

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

Re: analyzing logfiles

marcus_sommer — Wed, 30 Jul 2014 09:46:15 GMT

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

Re: Re: analyzing logfiles

dwarfsorg — Thu, 31 Jul 2014 09:19:02 GMT

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

Re: analyzing logfiles

Thu, 31 Jul 2014 09:42:02 GMT

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

Re: analyzing logfiles

marcus_sommer — Thu, 31 Jul 2014 10:37:15 GMT

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

Re: analyzing logfiles

dwarfsorg — Thu, 31 Jul 2014 12:54:55 GMT

Bingo !