Master Calendar Not linking correctly to Fact Table

[lucasdavis500]

I have one fact table (I concatenated 3 tables of data, because there is no (actual) Pkey, but wanted to simplify my data model, and eliminate Syntehtic keys).

My fact table has some fields that will align, i.e. Date Created, some other dates, etc. It seems that for the most part my fact table has produced desirable results ( i did most of my calculations with the tables separated, I have just recently tried to clean everything up).

During this process I have tried to create a Master Calendar.... It seems that it is functioning properly with two of the tables that were concatenated, but not with one of them, even though the master calendar is created off of the same field. has anyone else had this issues?

See picture below:

It's return NULL values for my table that contains a Pkey, but the other two, the two variables following Pkey that say Control Number, each have a Month, year, quarter, etc, linking them based on Date Created. I'm confused because Date Created is what I used to create the master calendar, so how can it be working for some data, (from two different tables) , but not the other

ex code:

Final_Data_Model:

LOAD

DATE([DateCreated]) AS [Date Created],

FROM $(vQVDPath)ABCData.qvd (QVD);

CONCATENATE

LOAD

DATE([Date Created]) AS [Date Created],

FROM $(vQVDPath)DEFData.qvd (QVD);

CONCATENATE

LOAD

DATE([Date Sent]) AS [Date Created],

FROM $(vQVDPath)GHIData.qvd (QVD);

IF NoOfRows('Final_Data_Model') > 0 THEN

STORE Final_Data_Model INTO $(vQVDPath)Final_Data_Model.qvd (QVD);

END IF ;

////// MASTER CALENDAR LOAD

QuartersMap:

MAPPING LOAD

ROWNO() AS Month,

'Q' & CEIL (ROWNO()/3) AS Quarter

AUTOGENERATE (12);

Temp:

LOAD

               MIN([Date Created]) AS minDate,

               MAX([Date Created]) AS maxDate

RESIDENT Final_Data_Model;

LET varMinDate = PEEK('minDate', 0, 'Temp');

LET varMaxDate = 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);

TempCal:

LOAD

date($(varMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE

    $(varMaxDate) - $(varMinDate) + 1;

MasterCalendar:

Load

               TempDate AS [Date Created],

               WEEK(TempDate) AS Week,

               YEAR(TempDate) AS Year,

               MONTH(TempDate) AS Month,

               DAY(TempDate) As Day,

               YEARTODATE(TempDate)*-1 AS CurYTDFlag,

               YEARTODATE(TempDate,-1)*-1 AS LastYTDFlag,

               INYEAR(TempDate, MONTHSTART($(varMaxDate)),-1) AS RC12,

               DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear,

               APPLYMAP('QuartersMap', MONTH(TempDate), NULL()) AS Quarter,

               WEEK(WEEKSTART(TempDate)) & '-' & WEEKYEAR(TempDate) AS WeekYear,

               WEEKDAY(TempDate) AS WeekDay

RESIDENT TempCal

ORDER BY TempDate ASC;

//DROP TABLE TempCal;

STORE MasterCalendar INTO $(vQVDPath)MasterCalendar.qvd (QVD);

[Anonymous]

In my opinion this isn't a drawback. A master calender is nothing more than a lookup table connected to fact tables via a certain field.

[Anonymous]

This won't work, because you just change the display format of the date, not the number behind the date itself.

[Peter_Cammaert]

Indeed, this isn't necessarily a drawback. All associations in QlikView are explicit, meaning that a field in one table has to contain the exact same values to link to the same field in another table. A Master Calendar only serves to reduce facts to specific days, months, quarters, years etc. As a consequence, you can keep your [Date Created] field in your facts table as-is - with time values - but to connect the master calendar to those same rows, you'll need a floored copy of that [Date Created] field. The new field (you can call it [%MasterCalendarKey] so that it never shows up in a field selection list if you disable System Fields) will act as a key field only, and will never show up in the UI in any objects at all if you don't want to. It only serves to make the Master Calendar work properly with your facts.

If you do want to connect your Master Calendar to the original [Date Created] field, you will have to create a Master Calendar that includes all possible time values, which boils down to 86399 additional Master Calendar records per day. I don't think you want to go that way...

[MK9885]

Doesn't he need to display hours, mins, secs?

It will change the number format to date and time format.