Master/Canonical Calendar & Calendar Object

[#1]

Hello,

I have two different date fields. I am trying to use a Master/Canonical Calendar to show date range in Calendar Object (StartDate, EndDate).

I created the attached QVW sample base on Rob's  Linking to two or more dates sample.

I can connect the "Date" field from Master Calendar to StartDate/EndDate Calendar Object. However, the result is incorrect. Because the "Date" field need to be used with "DateType" field. I need to add "DateType" to differentiate "OrderDate" and "ShipDate".

I tried to add =if(DateType = 'Order',Date)  into "Variable Event Triggers". But then my calendar object cannot work. I also tried to add the code =if (DateType = 'Order',min({1}Date)) into Calendar Object. But it doesn't work as well.

Can anyone who is familiar with this issue let me know how can I solve this issue?

Thanks in advance,

Becky

[NickHoff]

Why not just add OrderDate and ShipDate to your master calendar by doing Date AS OrderDate, Date AS ShipDate?  Then you won't need the date type.  You can get rid of the linked table if you go about it that way.

[#3]

Hi Nick,

Thanks for your response and idea.

I can get it work without using a Master Calendar. Just use OrderDate and ShipDate seperately with each Calendar Object associate with Variables. But after I complished it, I am now thinking if I can only use one data field but different DateType attributes to complete my goal. Just trying to find a better/simple way to do it...

[NickHoff]

You would still use the Master Calendar, just add OrderDate and ShipDate to the master calendar aliased from Date.

So it would be:

Date AS OrderDate,

Date AS ShipDate,

In your Master Calendar, the only thing you are doing is getting rid of the linked table, which is causing the issue by having both dates in one field which is Date.

[#5]

I am not sure how to do it for the entire script. But I can add:

CALL CalendarFromField('OrderDate', 'OrderCalendar', 'Order '); // Create Calendar for OrderDate
CALL CalendarFromField('ShipDate', 'ShipCalendar', 'Ship '); // Create Calendar for ShipDate

Then I will get different master calendars with seperate OrderDate and ShipDate.  I believe it can perform as the same fuction as you mentioned, correct?

[NickHoff]

That's not what i'm suggesting.  You are making it harder for yourself going about it that way.  Just keep your Order and Shipment table left alone in the original form.  Then on the master calendar table CommonCalendar, just add

Date AS OrderDate,

Date AS ShipDate,

IF you need to create a master calendar feel free to use the following script I added the OrderDate and ShipDate to it for you, you may have to change your fiscal dates, and min/max if you need them.

//Calendar:

LET vDateMin = Num(MakeDate(2010,1,1)); 
//LET vDateMax = Floor(MonthEnd(Today())); 
LET vDateMax = Num(MakeDate(2020,6,30)); 
LET vDateToday = Num(Today()); 
LET PD = Date(makedate(2010,1,15));
LET vFactor = 6;   //offset for calculating fiscal year

TempCalendar: 
//Left Keep (PayCore)
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber, 
Date($(vDateMin) + RowNo() - 1) AS TempDate 
AUTOGENERATE 1 
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

SCM_CALENDAR_MASTER: 
LOAD
Date(TempDate) AS CalendarDate, 
Date(TempDate) AS  %DATE_KEY,

Date(TempDate) AS OrderDate,

Date(TempDate) AS ShipDate,

// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth, 
WeekDay(TempDate) AS CalendarDayName, 
Week(TempDate) AS CalendarWeekOfYear, 
Month(TempDate) AS CalendarMonthName, 
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter, 
Year(TempDate) AS CalendarYear, 

// Calendar Date Names 
WeekName(TempDate) as CalendarWeekNumberAndYear, 
MonthName(TempDate) as CalendarMonthAndYear, 
QuarterName(TempDate) as CalendarQuarterMonthsAndYear, 

// Start Dates 
DayStart(TempDate) as CalendarDayStart, 
WeekStart(TempDate) as CalendarWeekStart, 
MonthStart(TempDate) as CalendarMonthStart, 
QuarterStart(TempDate) as CalendarQuarterStart, 
YearStart(TempDate) as CalendarYearStart, 

// End Dates 
DayEnd(TempDate) as CalendarDayEnd, 
WeekEnd(TempDate) as CalendarWeekEnd, 
MonthEnd(TempDate) as CalendarMonthEnd, 
QuarterEnd(TempDate) as CalendarQuarterEnd, 
YearEnd(TempDate) as CalendarYearEnd, 

// Combo Date Examples 
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear, 
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter, 
'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays, 

//Fiscal Dates
Num(Month(AddMonths(TempDate,$(vFactor))),00) AS FiscalMonthNum,
'FY ' & Right(Year(AddMonths(TempDate,$(vFactor))),2) AS FiscalYearDesc,
Year(AddMonths(TempDate,$(vFactor)))&'|'&Num(Month(AddMonths(TempDate,$(vFactor))),00) as %Fiscal_Key,
Year(AddMonths(TempDate,$(vFactor)))&'-'&Num(Month(AddMonths(TempDate,$(vFactor))),00) as FYRPR,
Year(AddMonths(TempDate,$(vFactor))) as FiscalYear,
'FY ' & Right(Year(AddMonths(TempDate,$(vFactor))),2)& ' Q' & Ceil(Month(AddMonths(TempDate,$(vFactor)))/3) AS FiscalYearQuarter

RESIDENT TempCalendar ORDER BY TempDate ASC; 

DROP TABLE TempCalendar; 

[NickHoff]

I just realized this would create a circular link,  I see what you are trying to accomplish.  Which you'd correct by using the following:

OrderID2OrderDate:

     Mapping Load OrderID, OrderDate From Orders;

OrderID2ShippingDate:

       Mapping Load OrderID, ShipDate From Shipping ;

     Load OrderID, Applymap('OrderID2OrderDate',OrderID,Null()) as CanonicalDate, 'Order' as DateType

          Resident Order;

       Load OrderID, ShippedDate as CanonicalDate, 'Shipped' as DateType

[sunny_talwar]

I am not 100 % sure what the issue here? Are you looking to create two different start and end dates here?

Order Start Date

Order End Date

Shipment Start Date

Shipment End Date

Is that the goal? I am not sure I understand the final goal here. Can you may be provide more details?

[#9]

Hi Sunny,

I created two different start and end dates here. One for Order, the other is for Shipment.

My goal is trying to use only one date field (Date) to enable both Order calendar objects and Shipment calendar objects working. I am currently using two date fields (OrderDate, ShipDate) to make those calendar objects work.

In addition, a werid thing is the results came back were different.  I got different results, with or without using master calendar to make calendar objects work.

[#10]

Thanks Nick. I am just not sure how to use one date filed ("Date" in my example, "CanonicalDate" in your example) to make this work.

Best

Becky