topic Re: For each file in filelist in QlikView

For each file in filelist

Anonymous — Tue, 25 Feb 2014 09:56:52 GMT

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"

Re: For each file in filelist

Tue, 25 Feb 2014 10:04:30 GMT

Johan,

As ypu wrote in the title:

For each file in filelist(XXX)

LOAD ...

from $(file)

Where XXX is your path & text

Fabrice

Re: For each file in filelist

christophebrault — Tue, 25 Feb 2014 10:06:02 GMT

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

));

Re: For each file in filelist

Anonymous — Tue, 25 Feb 2014 10:13:58 GMT

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

Re: For each file in filelist

Anonymous — Tue, 25 Feb 2014 11:05:16 GMT

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.

Re: For each file in filelist

Tue, 25 Feb 2014 11:07:55 GMT

syntax : filelist('path\search string')

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

Fabrice

Re: For each file in filelist

Anonymous — Tue, 25 Feb 2014 11:14:05 GMT

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

Re: For each file in filelist

Tue, 25 Feb 2014 11:39:31 GMT

use

'$(file)'

or [$(file)]

for spaces (or anything)

Fabrice

Re: For each file in filelist

Anonymous — Wed, 26 Feb 2014 13:06:07 GMT

and the filelist itself, is that ok?

It seems that the script is not going into that file

Re: For each file in filelist

Wed, 26 Feb 2014 13:21:33 GMT

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

Re: For each file in filelist

Clever_Anjos — Wed, 26 Feb 2014 13:23:16 GMT

Maybe this can be used as an example TaskAnalysis.qvw

Re: For each file in filelist

DavidFoster1 — Wed, 26 Feb 2014 14:19:09 GMT

I think you are misunderstanding what the filelist function does. It does not read the contents of a file. It reads the list of files in a directory/folder.

If you want to for...loop from a files contents

1) Load the file into a QlikView table

2) Concat the contents of the table into a variable which would then hold a comma separated list.(dont forget to concat single quotes (chr(39)) into the result).

3) use FOR EACH vFile IN $(vFileList)