Load data from the highest folder number

davidthorpester — Mon, 20 May 2019 15:23:38 GMT

Hi All,

I have data I want to load that gets stored in hourly folders. I want to load the latest hours worth of data.

File example (for 4am on 10th May): root\results\20190510\04\file.qvd

Even though there is a folder for every hour, not all hours dump a file. So I would like to load the latest file for that day. I hope this made sense! Is it possible to look in multiple folders for the latest file? or take the max folder number that has a file?

Thanks, David

Re: Load data from the highest folder number

zhadrakas — Tue, 21 May 2019 09:47:01 GMT

Hello,

here is a script for you. Maybe you need to adapt it to your needs.

Change the vPath to your Folder you want to scan. Note that if your select your Root Folder it will scan the subfolders too.

The script will scan all Folders and get the FilePath for each file.

Then it will choose the latest file and load only that.

regards

tim

//********************************************************************************** //Scan current Folder & Subs SUB doDir (dir) FOR EACH file in filelist('$(dir)' & '\*.qvd') ; FileName: first 1 Load FilePath() as FilePath, Subfield(FilePath(), '\', -3) as YYYYMMDD, Subfield(FilePath(), '\', -2) as HH, num(Subfield(FilePath(), '\', -3) & Subfield(FilePath(), '\', -2)) as CheckNumber FROM '$(file)'; NEXT file // Process subdirectories FOR EACH subdir in dirlist( '$(dir)' & '\*' ) CALL doDir('$(subdir)') NEXT subdir END SUB //********************************************************************************** //scan Root Folder SUB doRoot (root) FOR EACH subdir in dirlist( '$(root)' ) CALL doDir('$(subdir)') NEXT subdir END SUB //********************************************************************************** LET vPath = 'root\results'; Call doRoot('$(vPath)') //Only Keep Latest File FileToLoad: NoConcatenate Load max(CheckNumber) Resident FileName; inner join first 1 Load FilePath Resident FileName; LET vLatestFile = peek('FilePath', 0, 'FileToLoad'); //Load Latest File FINAL: Load * FROM $(vLatestFile) (qvd); //cleanup Drop tables FileName, FileToLoad; LET file=; LET subdir=; LET vPath=; LET vLatestFile=; //********************************************************************************** exit SCRIPT;

Re: Load data from the highest folder number

davidthorpester — Tue, 21 May 2019 10:36:24 GMT

Thanks so much Tim, I'll try this and let you know.

David

topic Re: Load data from the highest folder number in QlikView

Load data from the highest folder number

Re: Load data from the highest folder number

Re: Load data from the highest folder number