Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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;

 

5 Replies
Highlighted
Master
Master

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!”
Highlighted
Specialist II
Specialist II

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

Highlighted
Creator III
Creator 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.

Highlighted
Creator III
Creator III

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

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

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