Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Any one who knows how to add "tertials" (grouping of four and four month per calendar year) into this master calendar?
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(opening_date) as minDate,
max(opening_date) as maxDate
Resident Medlemsutvikling;
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);
MasterCalendar_medlemsutvikling:
Load
TempDate AS opening_date,
week(TempDate) As Week2,
Year(TempDate) As Year2,
Month(TempDate) As Month2,
Day(TempDate) As Day2,
YeartoDate(TempDate)*-1 as CurYTDFlag2,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag2,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12_2,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear2,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter2,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear2,
WeekDay(TempDate) as WeekDay2,
if(Year(TempDate)=Year(Today()), 1) as Thisyear2,
if(Year(TempDate)=Year(Today())-1, 1) as Lastyear2
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Hello Jon,
You could use the same approach you did for the Quarters and create a second mapping load:
map_Tertial:
Mapping LOAD RowNo() as Month, 'T' & Ceil (RowNo()/4) as Tertial
AutoGenerate (12);
Then, in the second part:
ApplyMap('map_Tertial', Month(TempDate), Null()) as Tertial2,
Cheers,
Philippe
Create the field in MasterCalendar_medlemsutvikling as 'T' & Ceil (Month(TempDate)/4) as Tertial
Hello Jon,
You could use the same approach you did for the Quarters and create a second mapping load:
map_Tertial:
Mapping LOAD RowNo() as Month, 'T' & Ceil (RowNo()/4) as Tertial
AutoGenerate (12);
Then, in the second part:
ApplyMap('map_Tertial', Month(TempDate), Null()) as Tertial2,
Cheers,
Philippe
Works perfectly!! Thanks
Already tried that. Returning the following error, thanks anyway.
I don't know what you tried, but my statement doesn't create or use any mapping.
I misunderstood your solution, seams like your solution works as well, thanks!