Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need my data model to filter Fact_Product QVD (containing monthly data for last 5 years) for the following SNAPSHOT_DATE values.
Last 3 months
Last 3 quarters
Last 4 year-ends
What are the ways to achieve that? I guess with variables? Or with MasterCalender?
Hi, Maybe something along these lines.
/*------Define variables for today's date and the start date (5 years ago)---------*/
LET vToday = Today();
LET vStartDate = Date(AddYears(Today(), -5));
/*------Create a temporary table with date ranges---------*/
TmpDate:
LOAD Date('$(vStartDate) '+ IterNo() - 1) AS Date
AUTOGENERATE 1
WHILE '$(vStartDate)' + IterNo() - 1 <= '$(vToday)';
/*------Create flags---------*/
NoConcatenate
FlagTable:
LOAD
If(Date >= AddMonths('$(vToday)', -2) and Date <= '$(vToday)', //Last3Months
If(Date >= AddMonths('$(vToday)', -8) and Ceil(Month(Date)/3) = Ceil(Month('$(vToday)')/3), //Last3Quarters
If(Year(Date) >= Year('$(vToday)') - 3 and Month(Date) = 12, Date,Null())) ) as SNAPSHOT_DATE //Last4YearEnds
Resident TmpDate;
Fact_Product:
LOAD * FROM [Fact_Product.qvd] (qvd)
Where Exists(SNAPSHOT_DATE ) ;
/*------Drop/Filter-------*/
DROP Tables TmpDate, FlagTable;
Hi, Maybe something along these lines.
/*------Define variables for today's date and the start date (5 years ago)---------*/
LET vToday = Today();
LET vStartDate = Date(AddYears(Today(), -5));
/*------Create a temporary table with date ranges---------*/
TmpDate:
LOAD Date('$(vStartDate) '+ IterNo() - 1) AS Date
AUTOGENERATE 1
WHILE '$(vStartDate)' + IterNo() - 1 <= '$(vToday)';
/*------Create flags---------*/
NoConcatenate
FlagTable:
LOAD
If(Date >= AddMonths('$(vToday)', -2) and Date <= '$(vToday)', //Last3Months
If(Date >= AddMonths('$(vToday)', -8) and Ceil(Month(Date)/3) = Ceil(Month('$(vToday)')/3), //Last3Quarters
If(Year(Date) >= Year('$(vToday)') - 3 and Month(Date) = 12, Date,Null())) ) as SNAPSHOT_DATE //Last4YearEnds
Resident TmpDate;
Fact_Product:
LOAD * FROM [Fact_Product.qvd] (qvd)
Where Exists(SNAPSHOT_DATE ) ;
/*------Drop/Filter-------*/
DROP Tables TmpDate, FlagTable;
Thank you, BrunPierre!
I took inpiration from your code, and ultimately used the code below to achieve the goal. I hope the readers look at both versions to pick what suits them!
It is a dummy example.
Let vMinDate = Num(Floor('31-05-2019'));
Let vMaxDate = Num(Floor('30-06-2024'));
/*------Create a temporary table with date ranges---------*/
TempCalendar:
Load Date(MonthEnd(Date#('$(varMinDate)', 'DD-MM-YYYY'), IterNo() - 1), 'DD-MM-YYYY') AS SNAPSHOT_DATE
AUTOGENERATE 1
WHILE MonthEnd(Date#('$(varMinDate)', 'DD-MM-YYYY'), IterNo() - 1) <= Date#('$(varMaxDate)', 'DD-MM-YYYY');
MasterCalendar:
Load *,
If(IsLast3Months = 1 Or IsLast3Quarters = 1 Or IsLast4YearEnds = 1, 'Y', 'N') As Final_Date_Selection
;
LOAD
SNAPSHOT_DATE,
QuarterEnd(SNAPSHOT_DATE) As QuarterEnd,
If(SNAPSHOT_DATE >= MonthEnd(AddMonths('$(vMaxDate)', -3)), 1, 0) AS IsLast3Months,
If(SNAPSHOT_DATE = QuarterEnd(SNAPSHOT_DATE)
And SNAPSHOT_DATE >= QuarterStart(AddMonths('$(vMaxDate)', -9))
And SNAPSHOT_DATE <= QuarterEnd('$(vMaxDate)'), 1, 0) AS IsLast3Quarters,
If(SNAPSHOT_DATE = YearEnd(SNAPSHOT_DATE)
And SNAPSHOT_DATE >= YearEnd(AddYears('$(vMaxDate)', -4)), 1, 0) AS IsLast4YearEnds
Resident TempCalendar
;