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.

[rubenmarin]

Hi, it counts rows on the table, so no rows added from SRO.

You can use NoOfRows to retrieve number of rows in table and use it in a "Trace" to check or in an "IF" to throw an error .

[user8]

Thanks Rubenmarin.

Could you advise how I can include the NoOfRows function into my incremental load script to show the number of new records added to the QVD file?

[rubenmarin]

Hi, After SRO you can use a variable to store the number of rows

LET vNumRowsBefore = NoOfrows('SRO');

And before store incremental do the operation to know the new rows added:

LET vNewRowsAdded = NoOfRows('Incremental')-$(vNumRowsBefore);

[Kushal_Chawda]

looks like this condition is not working

where CompletionDate > $(Last_Updated_Date)

try to format the date according to actual date format in DB table

try convert date in variable something like below

where CompletionDate >  to_date($(Last_Updated_Date) ,'DD MMM YY')

Note: Check the format of CompletionDate in DB and then accordingly based on function supported in DB format the date in variable

[user8]

Thanks for your help. 

With the NoOfrows I can view the number of new rows added but how can I verify that it should not fetch the existing rows from DB but only the new rows to QVD? I mean it could have fetch all the rows again and overwrite the existing data in the QVD. Any way to confirm it is only fetching the new rows to the QVD file? 

[rubenmarin]

Hi, vNewRowsAdded will store the difference in rows between the initial rows added from qvd and the final rows of the table, so it should count new rows added from db.

Or remove the "DROP Table SRO and add a field to db read: like If(Exists("Form Ref No"), 0 ,1) as isNewRow

Load this new files in a temp table: NoConcatenate LOAD * Resident Incrmental Where isNewRow=1)

Count row of this table with NoOfRows and then DROP SRO and temp table before the concatenate of the qvd.

[lorenzoconforti]

Looks like it is auto-concatenating when you are fetching the incremental data from SQL; try adding NoConcatenate; or as Kush141087 has said the condition might not be working

// Load incremental data
Incremental:
NoConcatenate LOAD
[Form Ref No],
[Form Name],
CompletionDate;

SELECT
"Form Ref No",
"Form Name",
CompletionDate
FROM vwSROHeader where CompletionDate > $(Last_Updated_Date);

[user8]

The Last_Updated_Date format is 12/27/2019
CompletionDate format in MS SQL DB is 27/12/2019 2:57:50 PM

So the Last_Updated_Date and CompletionDate date format are different i.e. 12/27/2019 vs 27/12/2019 2:57:50 PM and I think as you suspect this condition is not working i.e. where CompletionDate > $(Last_Updated_Date)

I changed as below:

where CompletionDate > to_date($(Last_Updated_Date),'MM/DD/YYYY');

But got an error:

The following error occurred:
Connector reply error: SQL##f - SqlState: 42000, ErrorCode: 195, ErrorMsg: 'to_date' is not a recognized built-in
function name.,

Please advise.

[lorenzoconforti]

to_date is not an SQL server function

Try changing the format before passing it to SQL

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

Then in your query:

where CompletionDate > '$(Last_Updated_Date_Formatted )';