Perform transformation on files in each subfolder

cgrocott — Tue, 18 Jul 2017 13:42:15 GMT

Hi,

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]:

Load

WeekStart([Date]) as Week_Start

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

(ooxml, embedded labels, table is Sheet1);

[Temp MinWeek]:

Load

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]

(qvd);

Re: Perform transformation on files in each subfolder

zhadrakas — Wed, 19 Jul 2017 07:42:34 GMT

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)')

regards

tim

topic Perform transformation on files in each subfolder in App Development

Perform transformation on files in each subfolder

Re: Perform transformation on files in each subfolder