Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I fetch data from files, the files are generated every day and every day I need to add one file more.
We want to limit file generation and not generate files for 2020 and 2021, what should such a regular expression look like?
The previous expressions we used to download files for 2020 and above:
FROM [$(vLevel2Path)\Items\Items202?????.qvd]
(qvd)
You may just switch to a filelist() load and query within it parts of the filename or maybe the filetime() or something else, like:
for each file in filelist('$(vLevel2Path)\Items\Items202?????.qvd')
if keepchar(subfield('$(file)', '\', -1), '0123456789') >= 20220101 then
t: load .... from [$(file)] (qvd);
end if
next
You may just switch to a filelist() load and query within it parts of the filename or maybe the filetime() or something else, like:
for each file in filelist('$(vLevel2Path)\Items\Items202?????.qvd')
if keepchar(subfield('$(file)', '\', -1), '0123456789') >= 20220101 then
t: load .... from [$(file)] (qvd);
end if
next
Thanks, we already have one solution with a loop that seems simpler (we define the current year and the year from which we start):
for yyyy=vStartYear to vCurrentYear
Items:
LOAD
*
FROM [$(vLevel2Path)\Items\*$(yyyy)????.qvd]
(qvd)
;
next // yyyy
The problem is that using loops, the app takes more data than it should take, but I don't see that there is any extra data.
That's why I wanted to make a regular expression, unless such "simple" solutions are not available in Qlik 😞
IMO the above suggested way of using a filelist() loop is a very simple and also an extremely powerful approach - because it's a general way to load any data.
It might be kept so simple like above but in may also be included within a dirlist() to scan multiple folder-hierarchies and there might be also more conditions be needed as extracting a period-information from the file-name else also n categories (also from the folder-names) and filesize/filetime and/or the exists of certain fields and reacting on it and/or more measures like copying/moving the files and many more.
Thanks a lot @marcus_sommer , after some time I understood what this list was about -> very useful option.