Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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"
Johan,
As ypu wrote in the title:
For each file in filelist(XXX)
LOAD ...
from $(file)
next
Where XXX is your path & text
Fabrice
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))
));
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
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.
syntax : filelist('path\search string')
for ex: filelist('D:MyPath\*.log')
Fabrice
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
use
'$(file)'
or [$(file)]
for spaces (or anything)
Fabrice
and the filelist itself, is that ok?
It seems that the script is not going into that file
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