topic Re: Master/Canonical Calendar & Calendar Object in QlikView

Master/Canonical Calendar & Calendar Object

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Master/Canonical Calendar & Calendar Object

NickHoff — Mon, 08 Aug 2016 19:30:47 GMT

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.

Re: Master/Canonical Calendar & Calendar Object

Mon, 08 Aug 2016 19:43:46 GMT

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...

Re: Master/Canonical Calendar & Calendar Object

NickHoff — Mon, 08 Aug 2016 19:51:03 GMT

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.

Re: Master/Canonical Calendar & Calendar Object

Mon, 08 Aug 2016 19:57:00 GMT

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?

Re: Master/Canonical Calendar & Calendar Object

NickHoff — Mon, 08 Aug 2016 20:01:13 GMT

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;

Re: Master/Canonical Calendar & Calendar Object

NickHoff — Mon, 08 Aug 2016 20:28:54 GMT

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

Re: Master/Canonical Calendar & Calendar Object

sunny_talwar — Mon, 08 Aug 2016 22:19:48 GMT

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?

Re: Master/Canonical Calendar & Calendar Object

Tue, 09 Aug 2016 13:08:33 GMT

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.

Re: Master/Canonical Calendar & Calendar Object

Tue, 09 Aug 2016 13:09:43 GMT

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

Re: Master/Canonical Calendar & Calendar Object

sunny_talwar — Tue, 09 Aug 2016 13:10:05 GMT

I am still not positive I understand the issue. Can you may be explain with some examples?

Re: Master/Canonical Calendar & Calendar Object

Tue, 09 Aug 2016 13:35:26 GMT

Hi Sunny,

Please see the attached sample QVW file.

Remember I was also asking about the alternate state? What I did in this sample shows Order info and Shipment info seperately controlled by states and master calendar. For the Order Tab, I only want to show info related to Order. Same to Shipment Tab.

I am trying to do it use the "Date" field to achieve my goal, instead of using "OrderDate" and "ShipDate" two date fields. Please let me know if you have more questions.

Thanks,

Becky

Re: Master/Canonical Calendar & Calendar Object

Tue, 09 Aug 2016 13:44:26 GMT

I think I may know how to do it...