Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ashis
Creator III
Creator III

How to get 12 months of data in incremental load

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;

 

Labels (2)
5 Replies
HirisH_V7
Master
Master

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

HirisH
“Aspire to Inspire before we Expire!”
anushree1
Specialist II
Specialist II

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

ashis
Creator III
Creator III
Author

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.

ashis
Creator III
Creator III
Author

Thank you Anushree for the document. Will look into it.
HirisH_V7
Master
Master

So we should have such condition on Period Column of your data. Where we should count 12 months back from current Month or it should be particular year.

Please clarify above and post a Sample app & data to avoid miss-communications. Make sure all the challenges are put into the sample.

-Hirish
HirisH
“Aspire to Inspire before we Expire!”