Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
cgrocott
New Contributor

Perform transformation on files in each subfolder

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

1 Reply
zhadrakas
Valued Contributor

Re: Perform transformation on files in each subfolder

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