topic Re: Rolling 3 years Data in QlikView

Rolling 3 years Data

raju_salmon — Thu, 05 May 2016 01:09:44 GMT

Hi,

My requirement is to hold the latest 3 years data in my application.

While doing full load in generator, i am using

LET vThreeyears=Addyears(today(),-3);

where modifieddate<=($vNow) and modifieddate>=($vThreeyears)

If i am doing incremental load daily once, every day one day records are added, so i need to remove 3 years back one day records. How can i remove one day records which is first day in my existing QVD (3 years back date)?

Any help on this? If i do it at generator level, i do not need to filter data at report level, so i can read optimized QVD.

Thanks,

Raju.

Re: Rolling 3 years Data

sunny_talwar — Thu, 05 May 2016 01:32:37 GMT

May be use this

LET vThreeyears=Addyears(today(),-3) + 1;

Re: Rolling 3 years Data

raju_salmon — Thu, 05 May 2016 01:37:03 GMT

Hi Sunny,

Thanks for reply, while doing incremental load i should maintain always latest 3 years data in my final QVD.

May i know where can i use above variable?

Thanks.

Re: Rolling 3 years Data

sunny_talwar — Thu, 05 May 2016 01:41:07 GMT

I guess you need to show what your current script is. Without knowing that, it would be difficult to suggest anything.

Re: Rolling 3 years Data

raju_salmon — Thu, 05 May 2016 01:53:48 GMT

I will try to send sample script soon.

Here is the more explanation.

I have last years data in my database.

if $(vLoadType)=0 // Full Load

Load

From Table

where where modifieddate<=($vNow) and modifieddate>=($vThreeyears) // i am taking data from 05/06/2013 to today.

if $(vLoadType)=1 // Incremental Load

I am taking latest one day records and adding to my existing qvd (as i have only inserted records)

After one year, i will have records from 05/06/2013 to 05/06/2017 (4 years data).

So, everyday i want to drop records those are available with least date in my existing QVD. If i add data of 05/05/2016 to my base QVD, then i need to drop records of 05/05/2013 from base QVD.

Hope it will give some idea.

Re: Rolling 3 years Data

sunny_talwar — Thu, 05 May 2016 02:02:05 GMT

Take a resident load where you drop the latest date from the cumulative qvd and then add that most recent data for today. Something along these lines

If $(vLoadType)=0 // Full Load

Table:

Load *

From DataSource.Table

Where modifieddate<=($vNow) and modifieddate>=($vThreeyears);

STORE Table into Main.qvd (qvd);

DROP Table Table;

If $(vLoadType) = 1

Table:

LOAD *

FROM Main.qvd(qvd)

Where modifieddate <> AddYears(Today(), -3);

Concatenate(Table)

LOAD *

FROM DataSource.Table

Where modifieddate = Today();

STORE Table into Main.qvd (qvd);