1 Reply Latest reply: Jul 19, 2017 3:42 AM by Tim Driller RSS

    Perform transformation on files in each subfolder

    Charlie Grocott



      apologies if this question is trivial, but I can't get my head around it and all the discussion I can find relates to Qlik View, not Sense.


      My query is this:


      I have a folder containing a number of subfolders. Each of these subfolders contains a number of files.

      For each subfolder, I want to load and perform a transformation on the files within that subfolder, then move on to the next one.


      I know need to use the For each... Next command, but cannot understand how to make it work. The use of the FileList with File as a variable is also confusing to me.


      If someone could help me with this, that would be fantastic.


      For reference, the command that I am performing is selecting the oldest week's data out of each file (each file contains multiple weeks data and a 'Date' field) and storing it into a QVD. This works fine, however I need to use the For Each...next command or it simply finds the oldest week overall, not for each file.


      Here is the existing script for reference, where TS testing is the path to the root data folder. This is probably an inefficient way to do it, but that's not what I'm worried about right now!:


      [Raw data]:



      WeekStart([Date]) as Week_Start

      From [lib://TS testing/TS*.xlsx]

      (ooxml, embedded labels, table is Sheet1);



      [Temp MinWeek]:


      Min(Week_Start) as MinWeekStart

      Resident [Raw data];



      Let vMinWeekTS = Date#(Peek('MinWeekStart'), 'DD/MM/YYYY');

      Let vMinWeekDate = Date(Peek('MinWeekStart'), 'DD-MM-YYYY');

      Drop Table [Temp MinWeek];


      [Retained TS]:

      NoConcatenate Load


      Resident [Raw data]

      where Week_Start = $(vMinWeekTS);


      Drop Table [Raw data];


      Store [Retained TS] into [lib://TS testing/Final week QVDs/TS_final_$(vMinWeekDate).qvd]


        • Re: Perform transformation on files in each subfolder
          Tim Driller

          Hello Charlie,


          here is a template for Looping through directories:


          //Process Directories
          SUB doDir (dir)
          //Process all .xlsx Files in current directory
          FOR EACH file in filelist('$(dir)' & '\*.xlsx') ;

          //Insert Your Script here

          NEXT file

          // Process subdirectories
          FOR EACH subdir in dirlist( '$(dir)' & '\*' )
          CALL doDir('$(subdir)')
          NEXT subdir
          END SUB

          SUB doRoot (root)
          FOR EACH subdir in dirlist( '$(root)' )
          CALL doDir('$(subdir)')
          NEXT subdir
          END SUB


          LET vRootFolder = 'C:\YourRootFolder\';
          Call doRoot('$(vRootFolder)')