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 Maintain constant 7 years of data in the Qvw...??

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

7 Replies
Highlighted
Partner
Partner

Hi Vishal,

try using Addyears() :

addyears ‒ QlikView

Highlighted

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator III
Creator III

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

Highlighted
Partner
Partner

You can also use AddMonths()

Using your example date

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

returns : 01-Feb-2007

Highlighted
Specialist III
Specialist III

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

Highlighted
Creator III
Creator III

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.

Highlighted
Specialist III
Specialist III

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