i'm wondering i'm creating this master calendar correctly, it seems to take quite a while on this part
Temp:
Load
min("Valuation Date") as minDate,
max("Valuation Date") as maxDate
Resident WR_MTD_COMB_DTL_F;
Here's the entire code, After all is said and done I really only care about month end date and i'd like to order it descending but that doesn't seem to work either. Any help would be great.
Thanks
WR_MTD_COMB_DTL_F:
LOAD *
FROM [lib://QVD/CombinedMTDDetailFact3.qvd]
(qvd);
QuartersMap:
MAPPING Load
rowno() as Month,
'Q' & Ceil(RowNo()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min("Valuation Date") as minDate,
max("Valuation Date") as maxDate
Resident WR_MTD_COMB_DTL_F;
// Temp:
// Load
// min(FieldValue("Policy Date",recno()))-1 as mindate,
// max(FieldValue("Policy Date",recno())) as maxdate
// AUTOGENERATE FieldValueCount("Policy Date");
Let varMinDate = Num(Peek('minDate',0,'Temp'));
Let varMaxDate = Num(Peek('maxDate',0,'Temp'));
DROP Table Temp;
TempCalendar:
Load
$(varMinDate) + Iterno()-1 as Num,
Date($(varMinDate)+Iterno()-1) as TempDate
AutoGenerate 1 While $(varMinDate)+IterNo()-1 <= $(varMaxDate);
ValuationCalendar:
Load
TempDate AS "Valuation Date",
DATE(monthend(TempDate)) AS "Valuation MonthEnd Date",
// week(TempDate) as "Valuation Week",
// Year(TempDate) as "Valuation Year",
// Month(TempDate) as "Valuation Month",
// Day(TempDate) as "Valuation Day",
ApplyMap('QuartersMap',month(TempDate),Null()) as "Valuation Quarter"
// Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as "Valuation Week Year",
// WeekDay(TempDate) as "Valuation Week Day"
Resident TempCalendar
Order By TempDate DESC;
Drop Table TempCalendar;
Store ValuationCalendar into [lib://QVD/ValuationCalendar.qvd](qvd);
Drop Tables ValuationCalendar;
