topic Re: Replace QVD file if new data published in QlikView

Replace QVD file if new data published

Wed, 25 Nov 2020 16:16:04 GMT

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 — Thu, 12 May 2016 21:10:15 GMT

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:

Fri, 13 May 2016 13:06:25 GMT

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 — Fri, 13 May 2016 15:10:53 GMT

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