Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
ashissau
Contributor 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;

 

5 Replies
HirisH_V7
Honored Contributor

Re: How to get 12 months of data in incremental load

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
Valued Contributor

Re: How to get 12 months of data in incremental load

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

ashissau
Contributor III

Re: How to get 12 months of data in incremental load

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.

ashissau
Contributor III

Re: How to get 12 months of data in incremental load

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

Re: How to get 12 months of data in incremental load

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!”