Incremental Load and FirstSortedValue

[theasfa]

Hi everyone.

So I have been trying to do an incremental load through a data connection by storing the original data in a QVD file (I will just refer to it as Historical.qvd in my load script), and I would like to get the distinct ID's based on the most recent modification date. My current script looks like this:

//Determine max date in historical data and create variable

LOAD Max(date_modified) as max_date_modified
FROM Historical.qvd;

Let max_date_modified = peek('max_date_modified', 0);
Let vMaxModDate = Timestamp( max_date_modified, 'YYYY-MM-DD');

//Create variable to filter to greater than max_date_modified

Let vModule = 'Module?filter[0][date_modified][$gt]=' & '$(vMaxModDate)';
Trace $(vModule);

// Get the new data

LIB CONNECT TO 'Data Source';

Daily:

LOAD

ID,

Name, 

date_modified,

// bunch more fields

Module;

SELECT 

ID,

Name,

date_modified,

//same fields as before

Module

FROM CustomModule
WITH PROPERTIES (
moduleName='$(vModule)',
max_num='',
order_by=''
);

// Concatenate new data to historical data

NoConcatenate

Concatenated_Table:
LOAD *

Resident Daily;

Drop Table Daily;

Concatenate

LOAD *

from Historical.qvd;

// Retrieve the most recent ID from concatenated table by using FirstSortedValue

NoConcatenate
Final:

LOAD

Name,

// same fields as before

Module,

Max(Timestamp(date_modified)) as date_modified,

FirstSortedValue(Distinct ID, -date_modified) as ID

Resident Concatenated_Table

Group by

Name,

// same fields as before

Module;

Drop Table Concatenated_Table;

Store Final into Historical.qvd;

// End of script

As a test I modified the variable so that the max date was a few weeks back, and it does seem to filter out most of the duplicate ID's, but not 100%. E.g. if it loads 300 lines of script from the new data, it might concatenate about 15 lines to the historical data, leaving me with some duplicate ID's. 

It would be great if anyone knows how to modify the script so that it is able to only retain the ID's with the most recent modification date. This is my first time using the FirstSortedValue, so I'm not sure if I'm using it correctly.

Thanks!

[fosuzuki]

So if what you want to do is just do the incremental for new and updated records, maybe you can do it simpler like this:

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT Exists(PrimaryKey);

STORE QV_Table INTO File.QVD;

(Extracted from Qlik help: https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/QVD_Incremental...

The key here is to use the EXISTS() function based on the IDs, to not consider from the historical QVD the new/updated records which were loaded from the database.