In this scenario, we must have a last modified date field in our source. The source can be any source supported by Qlik Sense, such as databases, spreadsheets, text files, and so on.
Every time you execute the script, you will check when the last record was last modified and load only records with a date greater than the last modified record date.
As an example, let's use the following table as the source:
%Id |
Country |
LastModifiedDate |
1 |
United States |
42972 |
2 |
United Kingdom |
42972 |
3 |
Canada |
42972 |
The source table has three columns: %Id, Country, and LastModifiedDate in Qlik Sense date format.
In the following code example, we will first check the most recent LastModifiedDate and we will then load all records with dates greater than LastModifiedDate:
/*I treat my table keys as system fields by hiding them. We do not want to use the keys in the front end and therefore we shouldn't show them.
*/
set HidePrefix='%' ;
/* I check if the file exists. If it doesn't I do a full load, if it does I perform the incremental load. */
IF IsNull(QvdCreateTime('C:myQVDCountries.qvd')) THEN
TRACE Performing full load of data source;
Countries:
SELECT * FROM COUNTRY;
STORE Countries INTO C:myQVDCountries.qvd (qvd);
ELSE
TRACE File exists. Performing incremental load;
maxDate:
LOAD max(LastModifiedDate) AS MaxModifiedDate
FROM c:myQVDCountries.qvd (qvd);
TRACE Storing the most current modified date into a variable.;
LET vMaxModifiedDate = peek(MaxModifiedDate);
TRACE Loading new records from the source.;
Countries:
SELECT * FROM COUNTRY
WHERE LastModifiedDate > $(vMaxModifiedDate)
;
TRACE Appending stored records to new ones.
Concatenate
LOAD * FROM c:myQVDCountries.qvd (Qvd);
TRACE Storing modified qvd file.;
STORE Countries INTO c:myQVDCountries.qvd (Qvd);
ENDIF
Note that we now start using variables. We will explain more about variables in the next chapter. We also use the Trace syntax. This will log messages in the output window to make it easier for you to follow the execution script.
You can try this example by creating a simple .txt file and then adding more records.
Remember that the LastModifiedDate can be formatted using the date() function but for scripting, it is recommended to have a numeric date format to avoid clashing.