Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I am trying to load 12 months of data in incremental load. I have a store procedure that has two data parameter, from date and to date. I need to pass this dates in a variable and fetch the data.
When I am loading the data first time , I would load 12 months of data without any issue. But when I want after the first (full load) it should load incrementally, in that logic I can append data from the last date to the current date, but struggling how do I delete data from the QVD. Please see my load script. Any suggestion and advice is much appreciate.
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;
Check out the QVW attached and implement as such.
or
make some amendments in example data and post your scenario and challenge facing their pl.
HTH,
Hirish
Deletion needs to be performed basis certain condition which states what are the eligible rows for Deletion please check the attached Document which has multiple scenarios for Incremental Load on Deletion and choose the one which suits your requirement
Hi Hirish, Thank you for prompt reply and the qvw you have put it. My requirement is bit different, where our data is not going to be duplicated and nor deleted from DB. I see how are you appending the QVD and deleting the old data. In my case I need to find out way so for every incremental load first I need it check it is loading 12 months of data .. and need to delete any data more than 12 months from the existing QVD. Currently It is appending more than 12 months of data in QVD.