Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vishalgoud
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
jpenuliar
Partner - Specialist III
Partner - Specialist III

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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
vishalgoud
Creator III
Creator III
Author

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
Partner - Specialist III
Partner - Specialist III

You can also use AddMonths()

Using your example date

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

returns : 01-Feb-2007

sasikanth
Master
Master

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
Creator III
Creator III
Author

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

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