Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I created an incremental load script as below:
// Loading data from QVD
SRO:
LOAD
[Form Ref No],
[Form Name],
CompletionDate
FROM [lib://SRO.qvd] (qvd);
// Find last modified date
Last_Updated_Date:
load max (CompletionDate) as Maxdate
resident SRO;
// Store last modified date to a variable
Let Last_Updated_Date=Date(peek('Maxdate',0,'Last_Updated_Date'));
TRACE >>> $(Last_Updated_Date);
// Delete Table SRO
Drop table SRO;
LIB CONNECT TO 'SQL_ODBC';
// Load incremental data
Incremental:
LOAD
[Form Ref No],
[Form Name],
CompletionDate;
SELECT
"Form Ref No",
"Form Name",
CompletionDate
FROM vwSROHeader where CompletionDate > $(Last_Updated_Date);
// Concatenate with QVD
Concatenate
LOAD
[Form Ref No],
[Form Name],
CompletionDate
FROM [lib://SRO.qvd] (qvd) where not Exists ("Form Ref No");
// Replace old QVD file
Store Incremental into [lib://SRO.qvd] (qvd);
// Drop Incremental Table
Drop table Incremental;
Started loading data
SRO << SRO
(QVD (row-based) optimized)
Lines fetched: 1,181 ====> There were 1,181 lines in the existing QVD file
Last_Updated_Date << SRO
Lines fetched: 1
>>> 12/9/2019
Incremental << vwSROHeader
Lines fetched: 1,189 ====> There were 1,189 lines in DB, which means 8 new lines will be added to the QVD file
Incremental << SRO
(QVD (row-based) optimized)
Lines fetched: 1,189 ====> Here it shows 1,189 lines fetched. Does this mean it fetched all the 1,189 lines again or just the 8 new lines to the QVD File?
Creating search index
Search index creation completed successfully
App saved
Finished successfully
Please refer to my comments in red as above. How do i verify the script is working as expected i.e. only fetching the new lines (8) and not all the lines (1,189) into the QVD?
Thank you.
CompletionDate is tagged as $numeric $timestamp.
The date format is 12/31/2019 11:29:34 AM when I viewed from Qlik data preview.
If I download the data to excel, it is shown as 31/12/2019 11:29:34 AM
Hi, it's on the 'LET' where you need to apply the timestamp:
Let Last_Updated_Date=Timestamp(peek('Maxdate',0,'Last_Updated_Date'), 'MM/DD/YYYY hh:mm:ss'));
In reload log (you can enable it in Document Properties->General tab->Generate log file) you can see the executed sql with the variable expanded, so you can copy the query to SQL to help wonder what's happening.