Load data from the highest folder number

[davidthorpester]

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

[zhadrakas]

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;

[davidthorpester]

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

David