You might like to also see this.
SET v_path = '<servername>\QVD\';
//the read of qvd's
For Each ext in 'qvd','QVD'
For Each file in FileList(path&'*.'&ext)
'$(file)' as FilePath,
FileTime('$(file)') as DateModify,
Date(FileTime('$(file)')) as Date
STORE [COLLECTION_QVD] into [COLLECTION_QVD.qvd] (qvd);
DROP Table COLLECTION_QVD;
Date >= MakeDate(Year(Now()-2),Month(Now()-2),Day(Now()-2)); //Clause of their choice.
Space load for their logic most recente date...
Thank you for your help. I have a better understanding of how I can load the latest file of a list, which I actually managed to do. I did the following
Set NullInterpret = '';
SET MoneyFormat='€ #.##0,00;€ -#.##0,00';
SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';
LET vPath = 'C:\Users\yno\Documents\Qlikview-Dashboard\';
LET vToday = date(today(),'DDMMYYYY');
LET vPositions = 'OTC-Posities';
LET vDTCCFX = 'DTCCFX';
LET vDTCCRATES = 'DTCCRATES';
LET vDTCCCOMMODITIES = 'DTCCCOMMODITIES';
load date(filetime(),'DDMMYYYY') as Filetimestamp,
FileName() as Filename,
date(Mid(Filename(),findoneof(Filename(),'-')+1,10),'MM-DD-YYYY') as datum //this is the position of the date in the filename of the reports
order by datum;
load date(max(datum),'DDMMYYYY') as maximaal
This allows me to load the latest file of each report. However, a problem arises when not all reports have the same date, this can actually happen, because I have noticed that sometimes certain reports aren't updated daily where others are. Since it is vital that all reports are from the same reporting date, I would like to use code to actually get the most recent date for which all reports for that date exist on the server. Do you have any suggestions for realizing this?
Isn't that a bit dangrous? I mean, if you load only the latest reports in different tables, and some reports may not exist on that date, then certain tables won't be created and this may cause problems further in your script or in the UI.
Or are you CONCATENATing all good csv files in a single large resident table? In that case you could use something like this instead of the last LOAD in your script:
FOR each vInputTable IN '$(vDTCCFX'', '$(vDTCCRATES), ... // Add the others
IF Not IsNull(FileTime('$(vPath)$(vDatum)$(vInputTable).csv')) THEN // file exists
BigTable: // Replace with [$(vInputTable)]: if you want separate tables
LOAD * FROM [$(vPath)$(vDatum)$(vInputTable).csv] (options);