applying incremental load in the below codition

[revoori123]

I have 6 years of data in my tables in that i want to present in the dashboard 2016 and 2017  when the new year 2018 starts ,need to drop the 2016 data  in Feb how can i implement in incremental load while storing the tables

[Sergey_Shuklin]

Hello!

You can use Today() function for indicating current month, like:

if(num(month(today()))=2,....)

And for indicating years to load:

where year >= year(addyears(today(),-1))

[srishsum2017]

please check the below link this is very helpful for you

https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

[MK9885]

You don't have to write Incremental script for this requirement.

At the time of extracting use

Where Year >= Year(Today())-6;

at the end of your script. this would only load Data for 6 years from current year.

and for the month Feb, is it static month for every year refresh or the months change?

[Mark_Little]

HI,

If you could share a little more of your Script and where you are loading from we could advise better.

But if you always want the last two year of data and not need to change each year add a where clause.

Load

   DATE,

FROM TABLE1

WHERE YEAR(DATE) >= YEAR(ADDYEARS(TODAY(),-2));

Mark

[revoori123]

Data1:

LOAD "DT",

"num_ID",---Primary Key

"CD",---Primary Key

"Indicator",--Primary Key

"int_Dec",

"Ended_ClMS",

from sql select * from Data1;

now i have data only for 2 years going forwarward it will increased to 6 years

Condition: until Jan 31st  need to store the data into QVD past 2 years i,e(2016,2017)

when feb 1 2018 start it will have only to store the data only for past one year i.e,, (2017).

Can you please help me out this problem

[revoori123]

Data1:

LOAD "DT",

"num_ID",---Primary Key

"CD",---Primary Key

"Indicator",--Primary Key

"int_Dec",

"Ended_ClMS",

from sql select * from Data1;

now i have data only for 2 years going forwarward it will increased to 6 years

Condition: until Jan 31st  need to store the data into QVD past 2 years i,e(2016,2017)

when feb 1 2018 start it will have only to store the data only for past one year i.e,, (2017).

Can you please help me out this problem

[revoori123]

Data1:

LOAD "DT",

"num_ID",---Primary Key

"CD",---Primary Key

"Indicator",--Primary Key

"int_Dec",

"Ended_ClMS",

from sql select * from Data1;

now i have data only for 2 years going forwarward it will increased to 6 years

Condition: until Jan 31st  need to store the data into QVD past 2 years i,e(2016,2017)

when feb 1 2018 start it will have only to store the data only for past one year i.e,, (2017).

Can you please help me out this problem

[MK9885]

Not sure if this would work or you looking for this but I tested it on my Mater Calendar and it worked fine.

I've modified different scripts to get the result....

I'm sure other people would have much better answer but it worked for me.

This would create QVD's for

Full Year-2016

Year 2017 until Jan-31st

And Previous Year full. (Currently it is storing 2016 again)

Of course you'd have to create Year out of DT

Year(DT) as Year in your Data1 table would give you Year field and use that Year field to create QVD's

Set vYear = 2016;

LET vYearMax = Year(Today());

LET vPreviousYear = Year(Today())-1;

Let vMonth = 'if(Month<=Month(Today())-10,Month)';

Let vMonths = 'if(Month<=Month(Today())-1,Month)';

Let vCount = noofrows('MasterCalendar');

For i=0 to $(vCount) -1

FOR i = $(vYear) to $(vYear)

NoConcatenate

TempCal:

LOAD *

RESIDENT Data1

WHERE Year = $(vYear)    ;

FOR B = $(vYearMax) to $(vYearMax)

NoConcatenate

TempCal1:

LOAD *

RESIDENT Data1

WHERE Year = $(vYearMax) and if( Year>= $(vYearMax), Month(DT) = $(vMonth))  ;

STORE TempCal into C:\Users\.......\Desktop\EXP\TempCal$(i).qvd (qvd);

STORE TempCal1 into C:\Users\.......\Desktop\EXP\TempCal$(B).qvd (qvd);

NEXT B

FOR J = $(vPreviousYear) to $(vPreviousYear)

NoConcatenate

TempCal2:

LOAD *

RESIDENT Data1

WHERE Year = $(vPreviousYear) ;

STORE TempCal2into C:\Users\.......\Desktop\EXP\TempCal_$(J).qvd (qvd);

NEXT J

DROP Table TempCal, TempCal1, TempCal2;