Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

For each file in filelist

Can someone help me with this?

With the for each command I don't need a FROM line, but how can I then use the conditional delete?

The txt file used here are the tasklog files from qlikview.

I want to read them all out and make an overview of what task is running from what time to what time and I also want to see of the duration of the task is increasing or not.

We are running daily more then 1300 jobs, devided over 2 servers, so it is difficult to do it manually.

For each "tasklog.txt" in Filelist ('D:\files.txt')



temp:

LOAD @1,

 
Left(@1,10) as Date,

 
Left(Right(@1,Len(@1)-11),8) as Time,

 
if(left(@3,5)='Start',Left(Right(@1,Len(@1)-11),8)) as starttime,

 
if(left(@3,6)='Saving',Left(Right(@1,Len(@1)-11),8)) as endtime,

    
@2,

    
@3,

    
SubField(@3,'"',2) as taskname,

    
if(left(@3,4)='Task',right(left(@3,len(@3)-8),8)) as duration,

    
@4

FROM
['D:\tasks\TaskLog.txt]

(
txt, utf8, no labels, delimiter is '\t', msq, filters(

Remove(Row, RowCnd(Compound,

RowCnd(CellValue, 3, StrCnd(contain, 'Starting task', not)),

RowCnd(CellValue, 3, StrCnd(contain, 'Task Execute Duration', not)),

RowCnd(CellValue, 3, StrCnd(contain, 'The task "', not)),

RowCnd(CellValue, 3, StrCnd(contain, 'Saving Task', not))

)),



ColXtr(3, RowCnd(CellValue, 3, StrCnd(contain, 'Starting task')), 0),

Replace(4, top, StrCnd(null))

));

Next "tasklog.txt"

11 Replies
Not applicable
Author

Johan,

As ypu wrote in the title:

For each file in filelist(XXX)

LOAD ...

from $(file)

next

Where XXX is your path & text

Fabrice

christophebrault
Specialist
Specialist

Hi,

Why don't you just try whith a * in your FROM instruction ? this with load every file in your dir :

temp:

LOAD @1,

 
Left(@1,10) as Date,

 
Left(Right(@1,Len(@1)-11),8) as Time,

 
if(left(@3,5)='Start',Left(Right(@1,Len(@1)-11),8)) as starttime,

 
if(left(@3,6)='Saving',Left(Right(@1,Len(@1)-11),8)) as endtime,

    
@2,

    
@3,

    
SubField(@3,'"',2) as taskname,

    
if(left(@3,4)='Task',right(left(@3,len(@3)-8),8)) as duration,

    
@4

FROM
['D:\tasks\*.txt]

(
txt, utf8, no labels, delimiter is '\t', msq, filters(

Remove(Row, RowCnd(Compound,

RowCnd(CellValue, 3, StrCnd(contain, 'Starting task', not)),

RowCnd(CellValue, 3, StrCnd(contain, 'Task Execute Duration', not)),

RowCnd(CellValue, 3, StrCnd(contain, 'The task "', not)),

RowCnd(CellValue, 3, StrCnd(contain, 'Saving Task', not))

)),



ColXtr(3, RowCnd(CellValue, 3, StrCnd(contain, 'Starting task')), 0),

Replace(4, top, StrCnd(null))

));

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
Anonymous
Not applicable
Author

Christophe

That is because they are all in a different folder, the date and the taskname are also a subfolder, but I removed the path for this example.

Johan

Anonymous
Not applicable
Author

How should my filelist (files.txt) look like?

When I now test my script, he doesn't find the first field, so the script is not going into the first txt file.

Not applicable
Author

syntax : filelist('path\search string')

for ex: filelist('D:MyPath\*.log')

Fabrice

Anonymous
Not applicable
Author

Now my filelist looks like this:

D:\QlikView\2Integration\Layout\System\Johan\tasks\folder1\TaskLog.txt

D:\QlikView\2Integration\Layout\System\Johan\tasks\Folder2\TaskLog.txt

D:\QlikView\2Integration\Layout\System\Johan\tasks\folder 3\TaskLog.txt

D:\QlikView\2Integration\Layout\System\Johan\tasks\folder4\TaskLog.txt

In folder 3 I added a space, I also wants this to work.

But the script isn't even going into the first one.

the script now looks like this:

each file in Filelist ('D:\QlikView\2Integration\Layout\System\Johan\tasks\files.txt')



temp:

LOAD @1,

 
Left(@1,10) as Date,

 
Left(Right(@1,Len(@1)-11),8) as Time,

 
if(left(@3,5)='Start',Left(Right(@1,Len(@1)-11),8)) as starttime,

 
if(left(@3,6)='Saving',Left(Right(@1,Len(@1)-11),8)) as endtime,

    
@2,

    
@3,

    
SubField(@3,'"',2) as taskname,

    
if(left(@3,4)='Task',right(left(@3,len(@3)-8),8)) as duration,

    
@4

FROM

$(file)

(
txt, utf8, no labels, delimiter is '\t', msq, filters(

Remove(Row, RowCnd(Compound,

RowCnd(CellValue, 3, StrCnd(contain, 'Starting task', not)),

RowCnd(CellValue, 3, StrCnd(contain, 'Task Execute Duration', not)),

RowCnd(CellValue, 3, StrCnd(contain, 'The task "', not)),

RowCnd(CellValue, 3, StrCnd(contain, 'Saving Task', not))

)),



ColXtr(3, RowCnd(CellValue, 3, StrCnd(contain, 'Starting task')), 0),

Replace(4, top, StrCnd(null))

));



STORE temp into ;

NEXT file

Not applicable
Author

use

'$(file)'

or [$(file)]

for spaces (or anything)

Fabrice

Anonymous
Not applicable
Author

and the filelist itself, is that ok?

It seems that the script is not going into that file

Not applicable
Author

use wildchars  like * to get several files

for each file in filelist('D:\xxx\*.txt')

...

next file

You can do also

... from 'D:\xxx\*.txt'

if you want to read all the files the same way

There are many examples in the community

Fabrice