Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

johan_ds
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

Re: For each file in filelist

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
Not applicable

Re: For each file in filelist

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))

));

johan_ds
Not applicable

Re: For each file in filelist

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

johan_ds
Not applicable

Re: For each file in filelist

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

Re: For each file in filelist

syntax : filelist('path\search string')

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

Fabrice

johan_ds
Not applicable

Re: For each file in filelist

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

Re: For each file in filelist

use

'$(file)'

or [$(file)]

for spaces (or anything)

Fabrice

johan_ds
Not applicable

Re: For each file in filelist

and the filelist itself, is that ok?

It seems that the script is not going into that file

Not applicable

Re: For each file in filelist

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