Incremental Load - Lines fetched

[user8]

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.

[user8]

Just wanted to clarify - does the Last_Updated_Date and CompletionDate format must be the same?
Currently Last_Updated_Date is 12/31/2019 and CompletionDate is 12/31/2019 2:57:50 PM

Changed as below:

LIB CONNECT TO 'SQL_ODBC';

let Last_Updated_Date_Formatted = date($(Last_Updated_Date), 'MM/DD/YYYY');

// 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_Formatted)';

Run results:

Started loading data
SRO << SRO
(QVD (row-based) optimized)
Lines fetched: 1,273
Last_Updated_Date << SRO
Lines fetched: 1
>>> 12/31/2019                                             ======> Last_Updated_Date value is 12/31/2019 
Incremental << vwSROHeader
Lines fetched: 1,273                                   ======> Here it is still fetching all the rows again. It should show 0 Lines fetched since there were no new rows.
Incremental << SRO
(QVD (row-based) optimized)
Lines fetched: 1,273
Creating search index
Search index creation completed successfully

Refer to my comments in green. It is still not working. I suspect it is not able to read the Date correctly in CompletionDate hence it is fetching all the rows from DB.

Any idea?

[lorenzoconforti]

Which DB server and version are you connecting to?

[user8]

It's MS SQL DB 2016.

[lorenzoconforti]

Then I think you need to use the MS SQL convert function

where CompletionDate > CONVERT(varchar,'$(Last_Updated_Date)',22);

or

where CompletionDate > CONVERT(varchar,'$(Last_Updated_Date_Formatted )',22);

[user8]

Thank you. The SQL convert function looks okay. Ran the load again:

Before running the incremental load:
QVD - 1178 rows
DB - 1274 rows
There are 96 new rows to be added to QVD file.

Running the incremental load:
Started loading data
SRO2 << SRO2
(QVD (row-based) optimized)
Lines fetched: 1,178
Last_Updated_Date << SRO2
Lines fetched: 1
>>> 12/9/2019
Incremental << vwSROHeader
Lines fetched: 110
Incremental << SRO2
(QVD (row-based) optimized)
Lines fetched: 1,272
Creating search index
Search index creation completed successfully

However the Lines fetched shows 110 when it should be 96.

Even when there are no new rows, the Lines fetched shows 3 as below:

Started loading data
SRO2 << SRO2
(QVD (row-based) optimized)
Lines fetched: 1,272
Last_Updated_Date << SRO2
Lines fetched: 1
>>> 12/31/2019
Incremental << vwSROHeader
Lines fetched: 3
Incremental << SRO2
(QVD (row-based) optimized)
Lines fetched: 1,272
Creating search index
Search index creation completed successfully

Any idea on the Lines fetched info discrepancy?

[lorenzoconforti]

Maybe those are rows inserted on the same day at a later time? The start date from your Qlik script is from midnight. Maybe you have records in SQL posted during the same day

Can you extract Qlik and SQL data to excel and compare there to see what's happening?

[user8]

The latest row item is dated 12/31/2019. Before running the load, I downloaded the rows from QVD and DB in excel as a reference. After the load I compared the rows and it did add 96 new rows to the QVD file. So I'm wondering why the Line Fetched info details showed a different figure.  

[lorenzoconforti]

My guess is that in Qlik your last row is 12/31/2019 but when you run your select your where statement is made on 12/31/2019 00:00 AM. So I think you might be reimporting records from SQL that occurred on the 31st (any time past 00:00AM)  which are already in your Qlik Database.

Can you compare the two Excel files (Qlik Vs SQL) before and after running the import to identify if there are indeed records that fall under this scenario?

Also, since your SQL timestamp is holding the time, I would do the same in Qlik and store time as well, not just the date

[user8]

Thanks for the explanation. I agree it will be good to store both date and time.

I tried below but the Last_Updated_Date still shows MM/DD/YYYY. 

// Find last modified date
Last_Updated_Date:
load max(timestamp(CompletionDate, 'MM/DD/YYYY hh:mm:ss')) as Maxdate
resident SRO; 

// Find last modified date
Last_Updated_Date:
load Timestamp(max(CompletionDate), 'MM/DD/YYYY hh:mm:ss') as Maxdate
resident SRO; 

Can you advise?

[lorenzoconforti]

When you check the attributes of the completionDate field in the data model viewer, does it show the timestamp as well?

Do the records in SQL have hour and seconds as well or are they just stored as dates?