3 Replies Latest reply: Feb 7, 2018 10:22 AM by Suraj R RSS

    Conditionally looping through all files in a folder

    Suraj R

      Hello hic, Hello All


      I have a query with regard to conditionally loading the files using a loop from a folder.


      Currently, I'm employing the same logic as mentioned in this post for loading my files from a folder: https://www.google.de/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0ahUKEwisy-euuofZAhXR5qQKHYbiBdMQFggnMAA&url=https%3A…


      However, would it be posssible to only load data files that correspond to a particular data range? I'm a newbie to Qlikview and intend to use it for analysing sensor data. However, I see that I have a lot of junk data which is meaningless to me. For example, if the files contain non-zero data on the y-axis after 8500ms on the x-axis, I do not want to load that file from the folder.


      Would it be possible to carry out this operation? f not possible, what could be the best way to tackle this problem?

      Have also attached a number of files for your reference.


      Would appreciate any help towards resolving this query.




        • Re: Conditionally looping through all files in a folder
          Henric Cronström

          If you just want to exclude some records, a simple where-clause will do the trick.


          But if you instead want to reject the entire file because of one singe record, you will need to load the file in a temporary table, then evaluate this table, and finally load (or not load) the table into your "real" data table. For example:


          Load Null() as DummyField AutoGenerate 1;

          sub ScanFolder(Root)
             for each FoundFile in filelist( Root & '\Test*.csv' )
             LOAD Count(X) as XCount
          FROM "$(FoundFile)" (txt, codepage is 1252, embedded labels, delimiter is ',', msq)
          Where "X [ms]">=8500 and Y2>0;                             
          Let FileIsOK = If(Peek('XCount',0,'tmpFile')>0,False(),True());
          If FileIsOK Then
          Concatenate (Data)
          FileName() as FileName,
          FROM "$(FoundFile)" (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
          End If
          Drop Table tmpFile;
          next FoundFile
          end sub

          Call ScanFolder('D:\Docs\Work\2018\Community\Suraj R\Files') ;


            • Re: Conditionally looping through all files in a folder
              Suraj R

              Hello hic,


              Many thanks for your help and the test solution. It works perfectly with the expectation that I had in mind


              With regard to improving the effectiveness of this analysis, I have 3 inputs for the same:

              1. Would it be possible to copy the files that do not contain the junk data onto a separate folder through the script?

              (For instance, into a new folder called Good Data. This would help in easier handling of the data)

              2. For some range of values of X (Eg: Between 1650 and 2000), how would we Need to Change the where condition for the same? (Tried to implement with 1650 < "X[MS]" < 2000, but it was faulty)

              3. Also, how can we implement a file counter to keep track of the number of files loaded?

              (I tried implementing as below, but it gives me the total files in the folder, not the count of files that we actually loaded.

              Looking forward to hearing from you and thanks once again.






              Screenshots (For all values of Y>0 at X<1500)





              • Re: Conditionally looping through all files in a folder
                Suraj R

                Hello Henric,


                I tried implementing a logic for the 1st query in the above post. But, it's not an effective implementation. Introducing the copying function inside the loop leads to multiple execution windows popping up after every iteration which is silly considering the number of files that are to be iomported.


                If we try using a loop outside the first for loop, how do I write the looping statement to copy the files read? Do we need to introduce another If condition again?(Tried giving the Loop Termination condition with respect to NoOfRows of files read, but it didn't work)


                Thanks and best regards,