Incremental load insert and delete

[ashishpalkar]

Hi There 

I am using following script for creating QVD. 

I have field name 'cobdate' where date is showing in this format '20180831'

I want to implement incremental load method , where date for the last 45 days will be store. 

(every day new date data  will be added to existing QVD and (today - 46th) day's data will be deleted from the QVD)

Any suggestion how to achieve this??

Allocation:
LOAD eventuuid,
taskuuid,
activityrunnumber,
businessidentifier,
transactionidtype,
cobdate,
businessdate,
legalentityid,
processingregion,
regioncode;
SQL SELECT eventuuid,
taskuuid,
activityrunnumber,
businessidentifier,
transactionidtype,
cobdate,
businessdate,
legalentityid,
processingregion,
regioncode
FROM `dhac_gw_secfin_pl_eur_view`;

Store Allocation into [lib://VRC_QVD/Allocation.qvd(qvd)];

drop table Allocation;

[dplr-rn]

Why not put a where condition in your sql to get data for last 45 days only?
other option is to resident load your table with a where condition. The former is preferable

[Digvijay_Singh]

May be like this - 

1. Load Data where COBDate = today()

2. Concatenate it with the existing QVD Data 

3. Delete Data where COBDate = Today()-46

4. Store table into QVD to refresh the existing QVD.

Thanks,

[Mauritz_SA]

What @Digvijay_Singh  said makes sense and would work. Having gone through the same exercise recently I can recommend the following:

1. Load data where the date is greater than your max date in the existing qvd to make sure that you don't miss chunks of data if there was a failed reload. If your IDs are auto generated in your SQL table then you can use the Max(Id) in your qvd for that rather than the date since the latter is normally indexed.

2. Steps 2&3 can be combined by only loading data from the existing qvd where COBdate >= Today()-45.

[ashishpalkar]

Hi

Can you please suggest , how to achieve following without resident load?

3. Delete Data where COBDate = Today()-46

[Mauritz_SA]

Hi there

When you append the old QVD to the newly loaded values, only load the values from the old QVD where COBDate > Today()-46.

Regards,

Mauritz