Discussion board where members can get started with QlikView.
I have made a QVD maker that creates QVDs every time a new XLSX file appears in a folder.
It follows this logic :
LOAD New file
STORE into QVD
So far this process works fine and I have a list of QVDs with the following format ' monthlysummary_DEPT_YYYYMM_Location.qvd'
Now, when one already created QVD file has to be changed (data errors etc) the only way is to delete all my QVDs and refresh the application.
The logic to prevent this step and a manual refresh is pretty simple :
IF my QVD FileTime is < my XLSX FileTime then replace my old QVD file with a new one.
IF (alt(FileTime('$(RootDir_FACT)monthlysummary_DEPT_*_$(v_File_Folder).qvd') < FileTime('$(RootDir_Data_Location_DEPT)*monthlysummary.xlsx'))) THEN
Now that formula doesn’t work because I am using wild card ‘ * ‘
For each QVD file there is a XLSX file related. Now, those files don’t have the same names and format, that makes it difficult to compare them.
Any idea how should I proceed ?
Go to Solution.
The basic idea would be to iterate over all QVDs, parse the QVD file name, construct the corresponding Excel file name and compare the file times:
FOR EACH vQVDFile IN FILELIST('$(RootDir_FACT)monthlysummary_DEPT_*_$(v_File_Folder).qvd')
Let vFileTemp = Textbetween( '$(vFile)','monthlysummary_','.');
Let vLocation = Subfield('$(vFileTemp)','_',3);
Let vYearMonth = Subfield('$(vFileTemp)','_',2);
Set vExcelFile = $(vYearMonth)_monthlysummary.xlsx;
Let vFileTimeCompare = FileTime('$(vQVDFile)') < FileTime('($(RootDir_Data_Location_DEPT)$(vExcelFile)');
// Now do your work based on the comparison:
IF vFileTimeCompare THEN
You may want to look into a FOR EACH vFile IN FILELIST('Path') ... NEXT vFile
loop to iterate over your qvds / excel files (I think QVD filenames could be derived from Excel file names, right?) and compare the file times:
For each..next ‒ QlikView
Thanks for your answer Stefan,
I understand the first part, not sure I know how I can compare my excel files and QVDs filetime.
Here is how they look like
QVD : monthlysummary_vDEPT_YYYYMM_Location.qvd [with this path before ('$(RootDir_FACT)]
EXCEL : YYYYMM_monhtlysummary.xlsx [with this path before ('$(RootDir_Data_Location_vDEPT)]