What's the best way to set controls on qvd files generated by an ETL process?

[sanadm19]

Hello guys,

We have 70+ qvd files affected everyday by QlikView generators under the daily ETL process. Most of them have incremental load set on them and quite a few of them are huge in size (more than a GB).

As part of the controls & alerts system, I currently have a qvw that checks the 'Date Modified' for each of these qvd files and sends out an email alert should a file be more than a day old. But this basic control mechanism just checks if the qvd was re-written on any given day. It doesn't tell us whether the qvd actually has the latest data in it. It could have gone thru the generator successfully but maybe there wasn't any fresh data coming from the source. So the question is - what would be the best way to make sure that we know if a qvd file received fresh data on a given day or not, regardless of the generator having successfully run.

Also if there's a way to find the most recent date it contains. I don't want to load up each qvd file and do a max date as it would overload the already dying server.

Many thanks in advance,

Sanad.

[jonathandienst]

I dont think that there is any simple QV way to determine the most recent date contained without reading the file. It may be possible to scan the symbol table at the start of the file* using some form of scripting (such as vbscript, javascript, Python), but such use of a qvd would be unsupported and undocumented.

* the symbol table contains the distinct values for each field and is located after the XML metadata at the start of the file.

[stabben23]

Hi,

if you search for qvd analyser or Governance Dashboard you will find Tools which could help.

[jonathandienst]

But scanning the file may not take that long. Something like

D_MAX:

LOAD max(Date) as maxDate

FROM ......qvd (qvd);

may not take as long as you might expect.

[marcus_sommer · Accepted solution]

I think there are various possibilities. One could be to build the incremental load in this way that without any new data the qvd won't be updated and then a check on the filetime would be valid check when the last refresh happend.

Another approach could be to read the number of records with qvdnoofrecords() and compare them with the stored value from the previous day.

Also the way Jonathan hinted by getting the max. value from the symbol-tables should work well whereby I would suggest to fetch this information within Qlik between loading the source-data and storing them as qvd. Here is a very fine explanation from Rob how it could be done: https://qlikviewcookbook.com/2013/09/fastest-method-to-read-maxfield-from-a-qvd/.

- Marcus

[jonathandienst]

Good ideas! I had seen that article from Rob but forgot about until you reminded me.

Adding some logging with the max Dates to the QVD generators is another possibility.

[sanadm19]

Thank you, Jonathan for your inputs

[sanadm19]

Hi Marcus,

You're right in suggesting the tweak to the incremental load where it wouldn't save the qvd in case of zero records and that would allow the date modified attribute to correctly tell if the file lacks fresh data. Will also try the max date load using the article from Rob.

Thank you!