Incremental load in qvd month wise qvd

[aditya_pratap]

Hi Everyone,@Michael_Tarallo @mbj @petter @richbyard @Mp3Leaks @rohitk1609 @Marcus_Spitzmiller @ergustafsson @Masaki_Hamano @Sue_Macaluso 

Good AfterNoon,

I am trying to find out how we can store qvd monthly and if suppose october month is going on and today i got new data so i have to incremental refresh october qvd and qvd name is ROOS_2022-10.qvd and november data came so it has to make new qvd with november data automatically. Kindly help me in this.

i have month,yearand date column for comparing .

i am using script but it is full load and pls find my script:

Load
year;

[ROOS]:
Select Distinct year From cocoblu_dwh.target_roos;

Let vCount = noofrows('ROOS');
For i=0 to $(vCount) -1
Let vYear = peek('year',$(i),'ROOS');

Load month_no;

[ROOS Month]:
Select Distinct year , month_no From cocoblu_dwh.target_roos;

Let sCount = noofrows('ROOS Month');
For j=0 to $(sCount) -1

Let sMonth = peek('month_no',$(j),'ROOS Month');

$(sMonth):
Select * from cocoblu_dwh.target_roos where year = $(vYear) AND month_no = $(sMonth);
Store $(sMonth) into [lib://Input_QVD_ROOS:DataFiles/ROOS_$(vYear)-$(sMonth).qvd];
//Load * from [lib://DataFiles/SIC_Daily_-$(vMonth).qvd];

Drop Table $(sMonth);

Next j

Next i

and pls help me in loading that data only for last 5 months qvd .

kindly help me in the script.

Thanks 

Aditya

[Dalton_Ruer]

Not sure if you are keeping the data in QVD's by their creation date, or by their modification date. I will go forward assuming your 2022_10.QVD has the data that was inserted/modified in October of 2022. If you also need to handle Deletions then thats a different issue I won't tackle in this reply. 

Think about breaking out these concepts into layers:

Layer 1: Some IncrementalLoad.QVF application handles the incremental loading of data. In that load script you would check for what month you are in. If today for example you know it's November 2022 so you load that QVD and you handle the incremental load and then restore 2022_11.QVD. If we run it the first time in December, you won't find the 2022_12.QVD so your code will have to do a file check to know that and then you will end up really doing a full load of your december data. 

Layer 2: Your application file contains some reference to how many months you want to go back in history and then begin your loading with the most recent month and work backwards. Each "Concatenate Load {my fields} from ... qvd would contain a WHERE NOT Exists clause. Thus you keep any rows from 2022_12 and concatenate any rows from 2022_11 that don't already exist. Then you concatenate any rows from 2022_10 that don't already exist. Etc. 

While you could obviously handle all of the coding in the same application breaking them allows you to have 1 application that has rolling 5 months, one that has rolling 13 months, one that has rolling 24 months etc.