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.
