Incremental load for 12 months of data

[ashis]

Hello,

I want to create incremental load for 12 months of data. I am calling a store procedure that has two date parameter , date from and date to.

When I am loading it first time (full load) I can pass 12 months back date in a variable and do its job. But I am struggling how I can do it when I am loading it incrementally. Please see my loading script. Any advice is much appreciate. In the incremental load I need to delete rows that are older than 12 months old .

Load script :

let vToday=date(now(),'YYYYMMDD');

let vFromDate=date(AddMonths(now(),-12),'YYYYMMDD');

set vFilePath=$(vSource)Data.qvd;
set vQVDExist=alt(QVDCreateTime('$(vFilePath)'),0);

if $(vQVDExist) >0 then

Incremental:
load *
from [..\XXX\Data.qvd](qvd);

Temp: 
Load
date(max(DATE),'YYYYMMDD') as maxDate 
Resident Incremental;

let vMaxDate=peek('maxDate');

drop Table Temp;

Concatenate

LOAD * ;
SQL SET NOCOUNT ON

Execute YYYYY  '$(vMaxDate)', '$(vToday)';

store Incremental into [..\XXX\Data.qvd ];

DROP table Incremental;

ELSE

Fullload:

LOAD * ;
SQL SET NOCOUNT ON

Execute YYYYY  '$(vMaxDate)', '$(vToday)';

store Data into [..\XXX\Data.qvd ];

DROP Table Fullload;

ENDIF;

[rahulpawarb]

May be this:

/* Check if the data file exists. If it doesn't then do a full load, if it does then perform the incremental load. */
IF IsNull(QvdCreateTime('$(vSourceQVD)Data.qvd')) THEN
TRACE Performing full load of data source;
Data:
LOAD
*;
SQL SELECT *
FROM owner.Data;

ELSE

TRACE File exists. Performing incremental load;

TRACE Storing the most current modified date into a variable.;

LET vMaxModifiedDate = Date(peek('MaxModifiedDate', 0, 'Tmp_MaxDate'));
LET vMaxModifiedDate = Date(peek('MaxModifiedDate', 0, 'Tmp_MaxDate'));

LET vFromDate = Date(AddMonths(now(),-12),'YYYYMMDD');
LET vToDate = Date(now(),'YYYYMMDD');

TRACE $(vFromDate);
TRACE $(vToDate);

TRACE Loading new records from the source.;

Data:
LOAD *;
SQL SELECT *
FROM owner.Data
WHERE 1 = 1
AND DATE >= '$(vFromDate)'
AND DATE <= '$(vToDate)';

TRACE Appending stored records to new ones;

Concatenate

LOAD *
FROM $(vSourceQVD)Data.qvd(qvd)
Where NOT Exists(sys_id);

ENDIF