how to Maintain constant 7 years of data in the Qvw...??

[vishalgoud]

Dear all ,

have a requirement where we have to maintain the constant 7 years of data even though the new month load happens once in every month.

I will load 7 years in the initial load, and then after that in every month new load will be loaded, here my issues is -  as new month load comes in then the last month data has to be deleted.

Ex: I have data from Jan 2007 to Dec 2014 , if I load the new month called Jan 2015 then Jan 2007 has to deleted automcatically inoder to maintain the constant data to 7 years.

Please suggest some method's to achieve this.

please let me know if you need any more information.

Best regards,

V

[jpenuliar]

Hi Vishal,

try using Addyears() :

addyears ‒ QlikView

[Anil_Babu_Samineni]

Please confirm me If Jan 2015 Data Added you want to remove only Jan 2007 OR Jan 2007 - Dec 2015 / WWhat?

[vishalgoud]

Hi anil,

thanks for the response, yeah here if we add new month called Jan 2015 then we have to delete only one month called Jan 2007 in order to maintain the total data of 7 Years.

so adding one month new data then we have to automate the deletion of one month (which is last in the Order of months).

[jpenuliar]

You can also use AddMonths()

Using your example date

=Date(AddMonths('01/01/2015',-95),'DD-MMM-YYYY')

returns : 01-Feb-2007

[sasikanth]

you can filer the condition some thing like

Load * from table

where  dateField>Addmonths(today(),-84) and Datefield<=Today()

Or

Date(DateField,'YYYYMM')>=AddMonths(Today(),-84,'YYYYMM') and Date(DateField,'YYYYMM')<=date(Today(),'YYYYMM')

[vishalgoud]

thanks sasi,

seems like your solution will work for us, but I want to filter the data in Qvd Generator it self in other words the first Qlikview app where we establish the connection with DB.

So we are unable to use Qlikview functions there we need to use SQL Queries to filter.

[sasikanth]

hi,

You can convert the dates into DB specific formats and use the same

If SQL SERVER use Convert function or ORACLE use TO_CHAR function

Ex:

Convert(varchar(30),Datefield,<format-Number>)