Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Honored Contributor II

Loop through log files

Hi All,

I have a hundreds of log files and I want to loop through them and calculate the duration between the start of a transaction and the end

below is an excerpt from the file, 4 lines, the first line is the start and the last line is the end, my files contain thousands of these

"02/06/2017" "08:38:35.008" "RECV" "prTaskLUTPicked('06-02-17 08:38:32.000','6315320077','Alan','Group ID','GC.D.7','D4D10','2','1','1','1','','','')<CR><LF<LF>"

"02/06/2017" "08:38:36.781" "SEND" "0,<CR><LF><CR><LF>"

"02/06/2017" "08:38:36.808" "RECV" "prTaskLUTGetPicks('06-02-17 08:38:34.000','6315320077','Alan','Group ID','0','0','0')<CR><LF<LF>"

"02/06/2017" "08:38:37.293" "SEND" "N,0,1,D4D10,0,,D 1,,0 1 0 2 D,7,Singles,0029203,0,0,0,0,033,,,description,,,GC.D.7,013,,0,,,,0,,,0,0,0,0,0,<CR><LF><CR><LF>"

I would like to create a table that contains data from the first line and the last line, the first line will always contain prTaskLUTPicked and the last line will always start with "N,0,1

"02/06/2017" "08:38:35.008" "RECV" "02/06/2017" "08:38:37.293" "SEND" "N,0,1,D4D10,0,,D 1,,0 1 0 2 D,7,Singles,0029203,0,0,0,0,033,,,description,,,GC.D.7,013

What is the best way to create this, Use Previous function, or create a temp table

Tags (3)
7 Replies
Honored Contributor

Re: Loop through log files

Hi Alan,

I copied your data into a txt file (log.txt) and ran this script



if(Status='Start', AutoNumber(@2),Peek(TransactionID)) as TransactionID,


LOAD @1,


if(Wildmatch(@4,'prTaskLUTPicked*'),'Start','End') as Status



(txt, codepage is 1252, no labels, delimiter is ' ', msq)

Where Wildmatch(@4,'prTaskLUTPicked*','N,0,1*');




Time(Min(@2)) as Start,

Time(Max(@2)) as End,

Interval(Max(@2)-Min(@2)) as Duration

Resident Log

Group by TransactionID;


TransactionID Start End Duration

Maybe you can load all your logfiles using a * wildcard, then if appropriate sort all the lines by the first time field then run something like the above script which takes out every line that is not a Start or End Time. Then it labels the rows with an autonumber to identify transactions then the Durations table is grouped on the TransactionID.

Good luck


Honored Contributor II

Re: Loop through log files

Hi Andrew,

Thanks for that, I am using a for each loop, to loop through all log files in the directory and sub directories.

I got what I need working using previous, but it's not very efficient, I will try your suggestion and get back to you tomorrow

Sent from my iPhone

Honored Contributor II

Re: Loop through log files

Thanks Andrew, it did not quite work the way I wanted, but it did give me some ideas, so thanks for your help.

MVP & Luminary
MVP & Luminary

Re: Loop through log files

Another approach could be to load at first the log-file completely and using then an outside peek() within a following load to access the first and the last record.

I could imagine that it might be slightly faster than the solution from Andrew because no where-clause is applied and no if-loop is needed to determine the first/last record. But I'm not sure that it - by hundreds of (assumingly rather small) files - made it bigger difference because the overhead for looping and accessing all the files is rather big compared to the amount of data.

- Marcus

Honored Contributor II

Re: Loop through log files

Hi Marcus/Andrew,

As part of my project I need to show the log file within the Application, so I have concatenated each row from the file into a single string called logfile and it looks like the below image


What I want to do next is to color the lines if the start time "07:52:47.050" minus the End Time is greater than 1 second and if it is, color all the lines between red, otherwise leave them green

The file contains hundreds of records and I am currently displaying them in a Pivot Table and trying to color the text based on the times, can it be done, I want to do this as a color expression.

The Pivot Table contains 1 Dimension called LogFile and 1 expression but it's blank, I am hoping it could be done from the color expression. Is it possible


I hope you can help.



MVP & Luminary
MVP & Luminary

Re: Loop through log files

I think I would tend to create this information with a flag-field within the script - just 0 and 1 on the record-level and using this to color the rows.

- Marcus

Valued Contributor II

Re: Loop through log files

Assuming you have Startdate and Enddate as separate field along with your data field  you can put the  condition like  below in the background Color  of the expression tab

if( timestamp(enddate-Startdate) >1 and ( wildmatch(Data,'*taslklupicked*' ,'*n,0,1*' )=0 , red(),green())