Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community,
I have made a QVD maker that creates QVDs every time a new XLSX file appears in a folder.
It follows this logic :
IF(isNull(qvdCreateTime('$(RootDir_FACT)monthlysummary_DEPT_$(v_File_HoroDate)_$(v_File_Folder).qvd'))) THEN
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 ?
Thank you
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
...
END IF
NEXT vFile
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:
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)]
Thanks
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
...
END IF
NEXT vFile