Master Calendar Not Reading Date Properly

[Anonymous]

Hi,

Using the Master Calendar to load the date field in the form of Day, Week, Year, etc shows a dash in some records while if I create an additional dimension for that record using the Day(), Week(), or Year(), then it works. Is there something wrong in my script?

LOAD
    "OPEN_TIME" as PROpenTime,
    Date( Floor( "OPEN_TIME" ) ) as PROpenDate;
SQL SELECT * FROM databasename;

// The Master Calendar
Load
     PROpenDate,
     week( PROpenDate ) As PROpenDateWeek,
     Year( PROpenDate ) As PROpenDateYear,
     Month( PROpenDate ) As PROpenDateMonth,
     Day( PROpenDate ) As PROpenDateDay,
     YeartoDate( PROpenDate )* -1 as PROpenDateCurYTDFlag,
     YeartoDate( PROpenDate , -1) * -1 as PROpenDateLastYTDFlag,
     date( monthstart( PROpenDate ), 'MMM-YYYY' ) as PROpenDateMonthYear,
     ApplyMap( 'QuartersMap' , month( PROpenDate ), Null() ) as PROpenDateQuarter,
     Week( weekstart( PROpenDate ) ) & '-' & WeekYear( PROpenDate ) as PROpenDateWeekYear,
     WeekDay( PROpenDate ) as PROpenDateWeekDay
Resident themaintable;

Here is a sample of the outcome:

You will notice that the second record is working properly while the first one is not.

Thank you for your response in advance.

[Anonymous]

Not working and even the correct record stopped working as well.

[ashok1203]

Day( date(Num#(PROpenDate),'DD/MM/YYYY' )) As PROpenDateDay,

[Anonymous]

It's working!!

I tried the following too but it didn't work. In the calendar, I added:

Day( Floor( PROpenDate ) ) as PROpenDateDay

Why putting in the original load statement works while the second one it doesn't?

[Anonymous]

Same result: both records are not working.

[jonathandienst]

I assume that PROpenDate is the date field in the fact table too, so it is the key linking the fact to the calendar. It looks likely that there are value of PROpenDate in the fact table that do not exist in the calendar (the first record).

Does "OPEN_TIME" cover all possible values of PROpenDate in the fact? I suspect not.

You can test this easily in the table viewer. Find and hover over PROpenDate in the calendar table and check that it is a key field (linked to other tables), and that the subset ratio is 100% (so all possible value exist in the calendar table).

[Anonymous]

Yes PROpenDate is the date field in the fact table and it is the key linking the fact to the calendar.

I tried to follow your advice and here is what I found:

I observed the following:

• The "OPEN_TIME" has null values against the PROpenDate although both of them are getting the data from the same field name.
• When I selected the value of 10/07/2014 in the PROpenDate, it showed me to records in the bottom table and one record in the upper one. Strange!

[Kushal_Chawda · Accepted solution]

I am not sure, what could be the exact reason, but instead directly taking resident try to create calendar based on min and max date

MinMax:

LOAD min(PROpenDate) as MinDate,

          max(PROpenDate) as MaxDate

Resident themaintable;

let vMinDate = peek('MinDate',0,'MinMax');

let vMaxDate = peek('MaxDate',0,'MinMax');

Calendar:

LOAD

week( PROpenDate ) As PROpenDateWeek,

     Year( PROpenDate ) As PROpenDateYear,

     Month( PROpenDate ) As PROpenDateMonth,

     Day( PROpenDate ) As PROpenDateDay,

     YeartoDate( PROpenDate )* -1 as PROpenDateCurYTDFlag,

     YeartoDate( PROpenDate , -1) * -1 as PROpenDateLastYTDFlag,

     date( monthstart( PROpenDate ), 'MMM-YYYY' ) as PROpenDateMonthYear,

     ApplyMap( 'QuartersMap' , month( PROpenDate ), Null() ) as PROpenDateQuarter,

     Week( weekstart( PROpenDate ) ) & '-' & WeekYear( PROpenDate ) as PROpenDateWeekYear,

     WeekDay( PROpenDate ) as PROpenDateWeekDay;

LOAD date($(vMinDate)+iterno()-1) as PROpenDate

autogenerate 1

While $(vMinDate)+iterno()-1 <= $(vMaxDate);

[jonathandienst]

I would not base my calendar on OPEN_TIME. Rather use the suggestion above by kush141087

[Anonymous]

Hi kush141087‌,

Thank you for your help!

I copied your code into my script. The result doesn't seem to be correct:

[Kushal_Chawda]

can you please share sample, so that it will be easy to catch the issue?