Conditional reload based on QVD update time

ToniKautto — Wed, 17 Apr 2024 13:44:32 GMT

It may be desirable to reload an app depending on whether all QVD files have been updated since the previous app reloaded. Below is an example of how this can be accomplished in the load script by comparing the last updated timestamp of QVD files with the app's previous reload time.

Environment

Qlik Sense Enterprise SaaS
Qlik Sense Enterprise on Windows
Qlik Sense Desktop
QlikView Server
QlikView Desktop

Previous successful app reload time

The simplest way to track the previous reload of an app is to store the current time in a variable at the end of the script execution. This variable value will only be set if the script execution successfully reaches the end and will be available for the following execution as a reference of the previous successful reload.

LET vPrevReload = UTC();

At the first reload of the app, the variable will not exist, meaning it is of zero length. In this case, it makes sense to initiate a really old timestamp.

IF Len($(#vPrevReload)) = 0 THEN LET vPrevReload = Date(0); ENDIF

QVD created time

QVD files always have an XML header representing metadata of its data content. QVD files are never updated when data changes. They are always overwritten when a STORE command is executed. For this reason, the XML header contains a field called CreateUtcTime, which is the time when the file was written or created.

It is possible to determine if the QVD file has been updated since the previous app reload by comparing the QVD created time with the previous reload variable. For simplicity, we flag the comparison result with a 1 when the file has been updated.

QvdFiles: LOAD If(CreateUtcTime > $(#vPrevReload), 1, 0) AS IsUpdatedFromPrevReload FROM [lib://MyFolder/MyDataFile.qvd] (XmlSimple, table is QvdTableHeader);

Checking multiple QVD files

Most apps are dependent on data from more than one QVD file. To check if multiple files have been updated, the files need to be named, for example, by simply listing the file path in an inline table. Notice, the path is a complete data folder connection reference.

QvdSource: LOAD @1 AS Qvd Inline [ lib://MyFolder/MyDataFile1.qvd lib://MyFolder/MyDataFile2.qvd lib://MyFolder/MyDataFile3.qvd ] (txt, no labels);

By iterating the timestamp comparison over all the QVD files, it is possible to decide if all files have been updated since the previous reload. This is done in two steps, first generate a comparison for each file, then aggregate the total result. The aggregated sum of all comparison results will represent how many files have been updated since the individual results are 1 when a file has been updated.

An aggregation of a data set in the load script requires grouping data by a field. For this reason, a dummy value that is the same for all records is introduced to create a single group of all records.

For Each vQvdFile in FieldValueList('Qvd') QvdFiles: LOAD 'All Files' AS GroupByAll, '$(vQvdFile)' AS QvdFile, If(CreateUtcTime > $(#vPrevReload), 1, 0) AS IsUpdatedFromPrevReload FROM [$(vQvdFile)] (XmlSimple, table is QvdTableHeader); Next NoOfUpdatedQvds: LOAD Sum(IsUpdatedFromPrevReload) AS NoOfUpdatedQvd Resident QvdFiles Group By GroupByAll;

Failing the reload

The total number of QVD files is expected to be the same as the number of updated files if the app should be reloaded. If the two values are different, then the reload should be avoided.

Important to notice that the reload must fail for the app to remain in its current state with data from the previous successful reload. This will allow a business user to keep consuming the app, even though the data might become a bit aged.
A simple way to fail a reload is to load from a non-existing data source, for example, by targeting a data connection that is unlikely to exist. Even if the data connection would exist, the load statement will fail by not specifying a target data file.

LET vUpdatedCount = Peek('NoOfUpdatedQvd', 0, 'NoOfUpdatedQvds'); IF $(#vUpdatedCount) <> NoOfRows('QvdSource') THEN LOAD * FROM lib://QvdFilesAreNotUpdated/ (QVD); ENDIF

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Re: Conditional relaod based on QVD update time

olgaavalos — Thu, 21 Mar 2024 19:04:51 GMT

Hi,

Thanks for sharing this qvf file. If I need to reload data only when the number of QVD records changes.

How would I change this script? Please help.

Re: Conditional relaod based on QVD update time

Sonja_Bauernfeind — Mon, 25 Mar 2024 10:28:51 GMT

Hello @olgaavalos

For detailed assistance with customizing scripts and achieving specific needs, please post your requirements directly in the Qlik App Development forum, where our active support agents and your knowledgeable Qlik peers can assist you.

All the best,
Sonja

article Conditional reload based on QVD update time in Official Support Articles

Conditional reload based on QVD update time

Environment

Previous successful app reload time

QVD created time

Checking multiple QVD files

Failing the reload

Re: Conditional relaod based on QVD update time

Re: Conditional relaod based on QVD update time