topic Re: How to select Data Ranges for File Exclusion and Copy Files to another Folder in QlikView

How to select Data Ranges for File Exclusion and Copy Files to another Folder

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

@Anonymous All,

I have a query with regard to setting a conditional expression and also enabling, copying files that I've not been able to figure out and require your help from the Qlik community to resolve the same.

Background:

My intention to use Qlikview is to suitably display data collected from a sensor. When reading this data continuously from a sensor, sometimes there are a number of files containing junk Information that distort the actual data to be analysed.

Eg: In the above figure, the files for which the data is marked in the red box are considered to be junk.

My intention is to:

a) Exclude those files that correspond to the junk data (Through the use of conditional expressions - Refer attached QVW) from being imported

Problem:

Right now, in the Where Statement, I'm able to view the chart only for conditional expressions that are singular (For Eg: ("X [ms]"< 1000 or "X [ms]"> 8500) and Y2>0). However, I'm not able to set a condition for in-between junk data range values (Eg: Region highlighted by red box in diagram i.e. When "X [ms]" is between 3900 and 5000 and Y2<2000). I tried writing it as ("X [ms]"> 3900 or "X [ms]"<5000) and Y2<2000), but it was faulty.

How must the expression be re-written to achieve the same?

b) Copy only those files that are imported and place it in a separate Folder (Eg: CleanData) or if not possible, only those files names of the loaded data and write that data onto a .txt file.

Problem:

I tried implementing a batch file to copy the files with each read similar to the one in diagram below, however, this is a very iterative process in the fact that for each Loop execution, a prompt window opens that needs to be clicked to proceed with ist execution and is not feasible when loading 100's of files. I tried writing this batch execution outside the loop, but it is incorrect and doesn't work correctly.

What could be the best method to achieve this?

Please let me know if any further information is required.

Looking forward to hearing from you.

marcowedel,‌ marcus_sommer, hic, cleveranjos - Any suggestions/ inputs would be most valuable

Regards,

Suraj

Note: Test files and script currently used for this implementation are attached.

Re: How to select Data Ranges for File Exclusion and Copy Files to another Folder

MarcoWedel — Tue, 13 Feb 2018 22:35:19 GMT

Hi,

one solution to exclude distorted files could be:

table1:
LOAD "X [ms]",
    Y1,
    FileBaseName() as FileName
FROM  (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

tabFileExclusion:
LOAD FileName,
    If(Min("X [ms]"> 3900 and "X [ms]"<5000 and Y1<2000), 'Exclude', 'Non-Exclude') as FileExclusion
Resident table1
Group By FileName;

hope this helps

regards

Marco

Re: How to select Data Ranges for File Exclusion and Copy Files to another Folder

MarcoWedel — Tue, 13 Feb 2018 22:49:24 GMT

one way to exclude those files might be:

table1:
LOAD "X [ms]",
    Y1,
    FileBaseName() as FileName
FROM  (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Right Join
LOAD FileName
Where not FileExclusion;
LOAD FileName,
    Min("X [ms]"> 3900 and "X [ms]"<5000 and Y1<2000) as FileExclusion
Resident table1
Group By FileName;

hope this helps

regards

Marco

Re: How to select Data Ranges for File Exclusion and Copy Files to another Folder

Anonymous — Wed, 14 Feb 2018 11:11:50 GMT

Hello Marco,

Yes, your solution to remove the junk files works perfectly as desired

One doubt though: With regards to separately storing the information of the files that are not-excluded (i.e. the name of the files) in a .separate text file , I tried using the Store function, but I'm only able to save the outcome of the file exclusion and the complete list of file names in the directory

How would we be able to store only file names that are not-excluded with this code?

For the other test code as well, since FileExclusion is not defined as a field value, it is not recognised and therefore, iton reload, it throws up an error and does not store the selected data contents.

Many thanks for your help again.

Best regards,

Suraj

Re: How to select Data Ranges for File Exclusion and Copy Files to another Folder

MarcoWedel — Wed, 14 Feb 2018 21:45:54 GMT

Hi,

after the Right Join script part the FileName field only contains non-excluded files, so you should be able to store the remaining FileNames and related data as is.

The FileExclusion field only is used as a temporary flag in my example.

regards

Marco

Re: How to select Data Ranges for File Exclusion and Copy Files to another Folder

Anonymous — Thu, 15 Feb 2018 08:12:55 GMT

Hi Marco,

Unfortunately, I'm still quite confused.

I tried earlier to use the store function with the filename field but there were a lot of copies of each of the considered files within the output file ( One file entry filled 2048 rows - Same as the total no of rows containing data within the file). I have attached the Output file (FinalTest.csv) and please find below the code used to execute the same.

Apologies for the numerous queries, but just want to clear my understanding and improve my learning.

Best regards,

Suraj