Incremental QVD with Date and Unique ID

[tkmr]

Trying to build an incremental script around non sequential dates and would appreciate some guidance.  The initial load would be the last week of information, but once built I would like to (1) load the remainder of the data (2) then update by date.  The table has both a transactiondate and a uniqueid (non sequential)

aggregate:

SELECT *

FROM "aggregate" WHERE transactiondate > current_date - ('1 weeks' :: interval);

STORE aggregate_qvd into [Lib://QVD Data Files (computer)/agg_qvd.QVD] (qvd);

Thanks in advance!

[Rodj]

The basic idea is to store the date you last loaded up to in another file (csv or qvd, doesn't matter) so that next time you can look it up and select the increment you want using that information.

if you do a quick search of the internet you'll find a host of examples and tutorials on this topic so unless there's a specific element of the process you are having trouble with I'd suggest starting there.

The basic logic will be something like:

Check to see whether your "last loaded date" table is populated, if not then

• Load your last weeks worth of data e.g, where transactionDate > today()-7
• Store the max transactionDate into your "last loaded date" table.

If this is a subsequent load (i.e. you detect the "last loaded date")

• Retrieve the last loaded date
• load from source table where transaction date > last loaded date

Now, if it is a straight incremental load you can concatenate the increment you've just loaded with a load of the previously loaded QVD table and store the complete table,

OR

you can store each increment separately and load them all together later if needed

OR (and it sounds like this might be want you want to do from what you've asked)

You can use a "where NOT exists" clause when you load your existing QVD to determine which records have a uniqueID that matches a record in your increment and then only store the records that weren't updated + your new updated records.

Hopefully that gets you started. Have a go and let us know if you run into specific challenges.