Hi All,
I have to merge three dates into one date: order date, start date, and finish date and I have to merge all of these three dates into one date called the implementation date. I have been suggested master calender and canonical date. I am trying to use master calendar but it keeps running into an error.
Temp:
Load
min(orderdate) as minDate,
max(orderdate) as maxDate
Resident mytable;
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);
orderdateCalendar:
Load
TempDate AS orderdate,
week(TempDate) As orderdateWeek,
Year(TempDate) As orderdateYear,
Month(TempDate) As orderdateMonth,
Day(TempDate) As orderdateDay,
YeartoDate(TempDate)*-1 as orderdateCurYTDFlag,
YeartoDate(TempDate,-1)*-1 asorderdateLastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as orderdateRC12,
date(monthstart(TempDate), 'MMM-YYYY') as orderdateMonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as orderdateQuarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as orderdateWeekYear,
WeekDay(TempDate) as orderdateWeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Thanks in advance,
!