topic Re: How to select certain date from filename using sub scanfolder function? in App Development

How to select certain date from filename using sub scanfolder function?

qliknewbie_admin — Thu, 11 Jul 2024 19:06:12 GMT

Hi,

At the moment, I am using sub scanfolder to get all files from different folder(its a yearly folder..inside this yearly folder got file based on month).

Now i got new request to only select few files based on certain month-year file.

I am stuck how to squeeze in date selection inside the loop based on the filename.

Request help from you guys.

below is sample of my script;

SUB Scanfolder(Root)

For each FileExtension in 'xlsx'

FOR Each FoundFile in FileList(Root &'\*.'& FileExtension)

T1:

LOAD ID,

NAMES,

SAL,

YEARS

FROM

[$(FoundFile)]

(ooxml, embedded labels, table is Sheet1);

Next FoundFile

NEXT FileExtension

For Each SubDirectory in DirList (Root & '\*' )

Call ScanFolder(SubDirectory)

Next SubDirectory

End Sub

Call ScanFolder('D:\Personal\LOADING DATA FROM MULTIPLE FOLDERS') ;

Re: How to select certain date from filename using sub scanfolder function?

TheLazyDevelopr — Thu, 11 Jul 2024 22:28:07 GMT

How is certain month year selected? Is it current month year?
You could try something with the FIleName function or just use a variable inside your filename that will only pull for the certain months/years you need.
Ex: FROM [lib://NS/ExcelFile*202407*.xlsx] or FROM [lib://NS/ExcelFile*$(vSelectedDate)*.xlsx]

https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/Scripting/FileFunctions/FileName.htm

Re: How to select certain date from filename using sub scanfolder function?

qliknewbie_admin — Thu, 11 Jul 2024 23:26:49 GMT

The file name is like below:

1. 31 December 2024.xlsx

2. 30 November 2024.xlsx

3. 31 October 2024.xlsx

4. 30 September 2024.xlsx

4. 31 December 2023.xlsx

2. 30 November 2023.xlsx

I only want to include 3 months below.

1. 31 December 2024.xlsx

2. 30 November 2024.xlsx

3. 31 October 2024.xlsx

Re: How to select certain date from filename using sub scanfolder function?

marcus_sommer — Fri, 12 Jul 2024 11:31:29 GMT

It's quite simple by querying the file-name per if-loop within the filelist-loop. At first the relevant date-part is extracted and converted to a real date and this is then queried with >= and <= of your wanted periods, for example with something:

if date(date#(subfield(subfield('$(FoundFile)', '\', -1), '.', 1), 'DD MMMM YYYY')) >= MyPeriod then
load ...
end if

Re: How to select certain date from filename using sub scanfolder function?

TheLazyDevelopr — Fri, 12 Jul 2024 14:41:59 GMT

Re: How to select certain date from filename using sub scanfolder function?

qliknewbie_admin — Wed, 31 Jul 2024 02:58:00 GMT

hi all, thanks for the reply.

i got it solved..all i need just simply use where clause to get the between dates.