Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Friends,
Kindly let me know your valuable suggestions. I have a master calendar created with a date field called PR_Date which is linked to fact table using PR_Date.
Min_Max:
LOAD
Min([_Date - PR]) as MinDate,
Max([_Date - PR]) as MaxDate
RESIDENT [Link];
LET vMinDate = Peek('MinDate', 0, 'Min_Max');
LET vMaxDate = Peek('MaxDate', 0, 'Min_Max');
DROP TABLE Min_Max;
[CalendarPR]:
LOAD DISTINCT
Dual(YearName(Temp_Date,0,$(vFM)), year(YearStart(Temp_Date,0,$(vFM))))
as [Year PR],
year(AddMonths(Temp_Date,-$(vFM)))
as [fYear PR],
YearName(Temp_Date,0,$(vFM))
as [fYearName PR],
Year(Temp_Date) as [CalendarYear PR],
Dual('Q' & ceil((Mod(Month(Temp_Date)-$(vFM), 12)+1)/3), ceil((Mod(Month(Temp_Date)-$(vFM), 12)+1)/3))
as [Qtr PR],
Date(WeekStart(Temp_Date),'YYYY-MMM-DD')
as [RWeek PR],
Date(WeekEnd(Temp_Date),'YYYY-MMM-DD')
as [RWeekEnd PR],
Div((Date(WeekStart(Temp_Date),'YYYY-MMM-DD')-Date(WeekStart(YearStart(Temp_Date,0,$(vFM))),'YYYY-MMM-DD') + 7),7)
as [WeekNo PR],
Week(Temp_Date) as [CalendarWeekNo PR],
Dual(Month(Temp_Date), Mod(Month(Temp_Date)-$(vFM), 12)+1)
as [Month PR],
Mod(Month(Temp_Date)-$(vFM), 12)+1
as [fMonth PR],
Month(Temp_Date) as [CalendarMonth PR],
Date(MonthStart(Temp_Date),'YYYY-MMM')
as [RMonth PR],
Day(Temp_Date) as [Day PR],
Date(Temp_Date) as [Date - PR],
Temp_Date as [_Date - PR]
;
LOAD DISTINCT
($(vMinDate) + IterNo() - 1)
as Temp_Date
AUTOGENERATE (1)
WHILE $(vMinDate) + IterNo() - 1 <= RangeMin($(vMaxDate),AddYears(Today(1),2));
LET vMinDate = Null();
LET vMaxDate = Null();
Let vFM=null();
Now they want to include another date field called PO_Date , so I created the same calendar with PO_Date in another tab and linked to fact table using PO_Date as follows
Min_Max:
LOAD
Min([_Date - PO]) as MinDate,
Max([_Date - PO]) as MaxDate
RESIDENT [Link];
LET vMinDate = Peek('MinDate', 0, 'Min_Max');
LET vMaxDate = Peek('MaxDate', 0, 'Min_Max');
DROP TABLE Min_Max;
SET vFM = 4;
[CalendarPO]:
LOAD DISTINCT
Dual(YearName(Temp_Date,0,$(vFM)), year(YearStart(Temp_Date,0,$(vFM))))
as [Year PO],
year(AddMonths(Temp_Date,-$(vFM)))
as [fYear PO],
YearName(Temp_Date,0,$(vFM))
as [fYearName PO],
Year(Temp_Date) as [CalendarYear PO],
Dual('Q' & ceil((Mod(Month(Temp_Date)-$(vFM), 12)+1)/3), ceil((Mod(Month(Temp_Date)-$(vFM), 12)+1)/3))
as [Qtr PO],
Date(WeekStart(Temp_Date),'YYYY-MMM-DD')
as [RWeek PO],
Date(WeekEnd(Temp_Date),'YYYY-MMM-DD')
as [RWeekEnd PO],
Div((Date(WeekStart(Temp_Date),'YYYY-MMM-DD')-Date(WeekStart(YearStart(Temp_Date,0,$(vFM))),'YYYY-MMM-DD') + 7),7)
as [WeekNo PO],
Week(Temp_Date) as [CalendarWeekNo PO],
Dual(Month(Temp_Date), Mod(Month(Temp_Date)-$(vFM), 12)+1)
as [Month PO],
Mod(Month(Temp_Date)-$(vFM), 12)+1
as [fMonth PO],
Month(Temp_Date) as [CalendarMonth PO],
Date(MonthStart(Temp_Date),'YYYY-MMM')
as [RMonth PO],
Day(Temp_Date) as [Day PO],
Date(Temp_Date) as [Date - PO],
Temp_Date as [_Date - PO]
;
LOAD DISTINCT
($(vMinDate) + IterNo() - 1)
as Temp_Date
AUTOGENERATE (1)
WHILE $(vMinDate) + IterNo() - 1 <= RangeMin($(vMaxDate),AddYears(Today(1),2));
LET vMinDate = Null();
LET vMaxDate = Null();
Let vFM=null();
It is working absolutely fine, but they want me to concatenate both as a single table instead of two. When I insert a concatenate between the two tables and reload the above code is giving an error.
Kindly let me know your valuable suggestions to make the above two table calendars into one table calendar based on both the date fields PR_Date and PO_Date.
Thanks so much and I sincerely appreciate your time.
Best regards,
Kiru
Hello Manish,
My apology for the delay. Thanks for the information, but is it possible to concatenate all the different master calendars into one single calendar?
Kind regards,
Kiru
You need to use Canonical Date which is creating a single calendar for different dates and using Flag to identify the different tables.
Hi,
Check this too.
Regards
ASHFAQ
Hi,
As to see your script it needs a common date if you create a common date and create a master calender by using canonical dates.
For more you can read this thread
Re: Master Calendar for Multiple Dates
Let me know if any thing requires.
Regards
Anand