Replace QVD file if new data published

[#1]

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

[swuehl]

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

[#3]

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

[swuehl · Accepted 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

...

END IF

NEXT vFile