Incrimental Load with an Update Date

[MalcolmCICWF]

We have a bunch of qvds set up with incremental loads at our company. Most of our qvds that load from SQL have a WHERE clause  grabbing any record where the CREATE_DATE is greater or equal to a variable date that is created and stored each reload. It then concatenates any records found with the qvd if a primary key does not exist (I believe this is a normal incremental load).

The issue I am seeing is that some of the SQL tables have an UPDATE_DATE where records can be updated if need be. This is written into some of the qvd loads, but because the primary key from this updated record exists in the qvd already, it does not get added. We are not adding updated records to the qvd, only those with new CREATE_DATE.

Anyone have an incremental script that works with two different dates fields?

Here is the current script (I removed the fields to shorten it):

////////////////////////////////////////////////////////////////////////////////////////

MaxDate:
load * from max_source_record.qvd (qvd);
let maxdate= text(date(peek('temp1',0,MaxDate),'YYYYMMDD'));
drop table pointless;

//////////////////////////////////////////////////////////////////////////////////////////

SOURCE_RECORD:
Load
   fields
;
SQL SELECT 
    fields
FROM dbo.table with (nolock)

/*comment for full reload*/ where (CONVERT(VARCHAR,CREATE_DATE,112) >= '$(maxdate)') or CONVERT(VARCHAR,CHANGE_DATE,112) >= '$(maxdate)';
/*comment for full reload*/ CONCATENATE LOAD * FROM SOURCE_RECORD.qvd (qvd) where not exists(PRIMARY_KEY);
;
STORE * FROM SOURCE_RECORD INTO SOURCE_RECORD.qvd (qvd);

DROP TABLE SOURCE_RECORD;

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

currentmax:
load *;
sql select max(case when CHANGE_DATE > CREATE_DATE then CHANGE_DATE else CREATE_DATE end) as temp1
from dbo.table;
STORE * FROM currentmax INTO max_source_record.qvd (qvd);
DROP TABLE currentmax;

[MalcolmCICWF]

Thank you Steve for finally clearing this up for me. This is very helpful as I was under the wrong impression. This is the only thing I could be thinking of that would be causing some of the missing records we are experiencing. I cannot determine what records they are, I just know I have to d a full reload every month or two in order to ensure all records are accounted for.

[stevedark]

What I would look at doing is archiving the QVD before you do a full refresh and create a new QVD.  You can then compare the IDs and content of the two QVDs to see exactly which rows were omitted.  This should then point you in the direction of the issue.

If the missing ones all seem to have been updated around the time of your loads then the getting of the last date is a likely culprit.  Time zones and system clocks being out of sync could be another.

Depending on how long your load takes and what your refresh rate and window of opportunity for loading are you may want to consider putting in an overlap with the times.  Perhaps always go an hour, or even twenty four hours, back on the date you know you last pulled records from.  Just as the WHERE NOT EXISTS removes records that have been updated it will also remove records that have been pulled more than once because of your overlap in last load date.

You have the luxury of being able to do this because you have a unique ID.  Where there is no unique ID it is usually a good idea to create one, in order that you can then have this kind of incremental routine.

Steve

[MalcolmCICWF]

Thanks Steve, I will keep looking into it to do some comparisons.

You were very helpful and the reason I love the Qlik Community.