Conditionally looping through all files in a folder

[Anonymous]

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-euuofZAhXR5qQKHYbiBdMQ...

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.

Regards,

Suraj

[hic · Accepted solution]

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:

Data:
Load Null() as DummyField AutoGenerate 1;

sub ScanFolder(Root)
   for each FoundFile in filelist( Root & '\Test*.csv' )
   tmpFile:
   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)
      LOAD
         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') ;

HIC

[Anonymous]

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.

Regards,

Suraj

Note:

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

Before:

After:

[Anonymous]

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,

Suraj