topic Only load files between certain filetimes? in QlikView

Only load files between certain filetimes?

RJW — Thu, 25 Aug 2022 19:45:11 GMT

I am working on a project in QlikView that needs to import text files from a folder. Within this folder, there are many subfolders organized by year and month.

However, this folder has many years of data. 90% of this data I do not need. Instead, I want to be able to type in the dates I need and have QlikView return the files that have a filetime between those dates.

The load works fine as it is, but it brings in everything, takes a long time, and I want to limit it on the script side. Currently, I am prompting the user for the dates but not sure how to implement the date variables entered into an if statement for my load. Each time I try I get errors.

______________________________________________________________________

My proposed solution would look something like this but it doesn't work. It gives and error "The control statement is not correctly matched with its corresponding start statement ..... next File":

sub ScanFolder(Root)
for each Ext in 'txt'
for each File in filelist (Root & '\*.txt')
if FileTime([$(File)]) > vStartDt AND FileTime([$(File)]) < vEndDt then
Data:
$(Concatenate)
LOAD [@1:50],
[@51:80],
[@81:97],
[@98:109],
[@110:n]
FROM [$(File)](fix, codepage is 28591, embedded labels);
set Concatenate = concatenate;
ELSE
next File
next Ext

for each SubDirectory in dirlist(Root&'\*')
call ScanFolder(SubDirectory)
next SubDirectory

end sub

Re: Only load files between certain filetimes?

rwunderlich — Thu, 25 Aug 2022 22:10:07 GMT

Your ELSE statement should instead be an ENDIF statement.

-Rob

Re: Only load files between certain filetimes?

Vegar — Thu, 25 Aug 2022 22:28:11 GMT

Could i æt be that your FileTime([$(File)]) expects a string as an parameter and not a field reference. Try changing to this: FileTime('$(File)')

Re: Only load files between certain filetimes?

RJW — Fri, 26 Aug 2022 18:11:22 GMT

Hi Rob, thanks. I made this change and now I get no errors when running the script!

However, now, it does not return any data. I know that the files exist with parameters I am typing in.

Re: Only load files between certain filetimes?

RJW — Fri, 26 Aug 2022 18:15:51 GMT

Hi Vegar,

Qlik seems to like this change, but I am not returning any data. In the parameters I am typing in 07/22/2022 as the vStartDt and 08/18/2022 as the vEndDt and expecting to get all text files from the directory that have been saved in that date range.

Re: Only load files between certain filetimes?

RJW — Tue, 30 Aug 2022 14:43:18 GMT

SOLVED: Needed to match the FileTime date format with the variable date format. The FileTime format included hh:mm:ss and the variable did not.

The input now reads:

LET vStartDt = Date(Input('Enter the last close date in MM/DD/YYYY format.', 'Please answer the following prompt.'),'MM/DD/YYYY hh:mm;ss');
LET vEndDt = Date(Input('Enter the most recent close date in MM/DD/YYYY format.', 'Please answer the following prompt.'), 'MM/DD/YYYY hh:mm;ss');

And, from there, the code returns the desired files within those dates. The code also includes the solutions suggested from @Vegar and @rwunderlich to help with the IF statement.

Thank you!