Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
suraj_ramachand
New Contributor II

Conditionally looping through all files in a folder

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

1 Solution

Accepted Solutions
Employee
Employee

Re: Conditionally looping through all files in a folder

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

3 Replies
Employee
Employee

Re: Conditionally looping through all files in a folder

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

suraj_ramachand
New Contributor II

Re: Conditionally looping through all files in a folder

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:

suraj_ramachand
New Contributor II

Re: Conditionally looping through all files in a folder

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

Community Browser