5 Replies Latest reply: Feb 15, 2018 3:16 AM by Suraj R RSS

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

    Suraj R

      @Hello 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, cuv - 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
          Marco Wedel

          Hi,

           

          one solution to exclude distorted files could be:

           

          QlikCommunity_Thread_291149_Pic1.JPG

          QlikCommunity_Thread_291149_Pic2.JPG

           

          table1:
          LOAD "X [ms]",
              Y1,
              FileBaseName() as FileName
          FROM [D:\Daten\QlikView\QlikCommunity\QlikCommunity_Thread_291149\Test*.csv] (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
            Marco Wedel

            one way to exclude those files might be:

             

            table1:
            LOAD "X [ms]",
                Y1,
                FileBaseName() as FileName
            FROM [D:\Daten\QlikView\QlikCommunity\QlikCommunity_Thread_291149\Test*.csv] (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
                Suraj R

                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