11 Replies Latest reply: Feb 26, 2014 9:19 AM by David Foster RSS

    For each file in filelist

    Johan De Schutter

      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

          Johan,

           

          As ypu wrote in the title:

          For each file in filelist(XXX)

          LOAD ...

          from $(file)

           

          next

          Where XXX is your path & text

           

          Fabrice

            • Re: For each file in filelist
              Johan De Schutter

              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

                  syntax : filelist('path\search string')

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

                   

                  Fabrice

                    • Re: For each file in filelist
                      Johan De Schutter

                      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 [D:\QlikView\2Integration\Layout\System\Johan\tasks\temp.qvd];

                       

                      NEXT file

                       

                • Re: For each file in filelist
                  Christophe Brault

                  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
                    Clever Anjos

                    Maybe this can be used as an example TaskAnalysis.qvw

                    • Re: For each file in filelist
                      David Foster

                      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)