Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
May be use this
LET vThreeyears=Addyears(today(),-3) + 1;
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.
I guess you need to show what your current script is. Without knowing that, it would be difficult to suggest anything.
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.
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);