topic Re: applying incremental load in the below codition in QlikView

applying incremental load in the below codition

revoori123 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: applying incremental load in the below codition

Sergey_Shuklin — Thu, 02 Nov 2017 09:52:01 GMT

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))

Re: applying incremental load in the below codition

srishsum2017 — Thu, 02 Nov 2017 10:01:23 GMT

please check the below link this is very helpful for you

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

Re: applying incremental load in the below codition

MK9885 — Thu, 02 Nov 2017 14:44:55 GMT

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?

Re: applying incremental load in the below codition

Mark_Little — Thu, 02 Nov 2017 15:39:52 GMT

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

Re: applying incremental load in the below codition

revoori123 — Fri, 03 Nov 2017 06:16:03 GMT

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

Re: applying incremental load in the below codition

revoori123 — Fri, 03 Nov 2017 06:16:14 GMT

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

Re: applying incremental load in the below codition

revoori123 — Fri, 03 Nov 2017 06:16:54 GMT

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

Re: applying incremental load in the below codition

MK9885 — Fri, 03 Nov 2017 17:38:11 GMT

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;