Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Replace QVD file if new data published

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Replace QVD file if new data published

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

3 Replies
MVP
MVP

Re: Replace QVD file if new data published

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

Not applicable

Re: Replace QVD file if new data published

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

MVP
MVP

Re: Replace QVD file if new data published

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