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