topic Master Calendar Not linking correctly to Fact Table in QlikView

Master Calendar Not linking correctly to Fact Table

lucasdavis500 — Wed, 25 Nov 2020 16:16:04 GMT

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);

Re: Master Calendar Not linking correctly to Fact Table

MK9885 — Tue, 08 Nov 2016 19:08:29 GMT

Can you share the sample data or sample qvw?

Re: Master Calendar Not linking correctly to Fact Table

lucasdavis500 — Tue, 08 Nov 2016 19:12:00 GMT

The picture shows sample data. I don't believe I'll be able to share a sample QVW unless I really took everything out of it... The weird thing is, is that for SOME of the data, where Pkey IS NOT NULL, there are values for Month, Year, etc. but for 99% of them, all date fields (aside from Date Created) is NULL.... But with both Control Number fields, every Date Created field has a matching Month, year, quarter, etc...

Re: Master Calendar Not linking correctly to Fact Table

Anonymous — Tue, 08 Nov 2016 19:12:10 GMT

just change the format of "date created" to number

and post the result the following table again

Re: Master Calendar Not linking correctly to Fact Table

MK9885 — Tue, 08 Nov 2016 19:17:54 GMT

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

where not "[Date Created]"='';

////// MASTER CALENDAR LOAD

// Date Dimension

// to load Quarters Full Name

QuarterNAME:

LOAD * Inline [

Quarter , QuarterFullName

Q1 ,FIRST

Q2 ,SECOND

Q3 ,THIRD

Q4 ,FOURTH

];

// to create Quarters ie Q1,Q2

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

varMinDate = num(date(mid('10-24-2016',1,10 ),'MM-DD-YYYY'));

varMaxDate = num(date(today(),'MM-DD-YYYY'));

// Creating a Temporary Calendar

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 as Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

// Date Dimension

MasterCalendar:

LOAD*,

AutoNumber ([Year Month Num], 'PeriodID') as PeriodID,

AutoNumber (Year & Quarter, 'QuarterID') as QuarterID;

Load

trim(date(TempDate,'YYYY-MM-DD')) as [Date Created],

date(TempDate,'YYYY-MM-DD') as [Effective Date],

day(TempDate) as Day,

TempDate as [US Calendar Format],

date(TempDate,'YYYY-MM-DD') as [UK Calendar Format],

date(TempDate,'WWWW') as [Full Day Name],

year(TempDate) as Year,

inyear(TempDate,today(),0) * -1 as [CY], // Current Year

inyear(TempDate,today(),-1) * -1 as [First PY],

inyear(TempDate,today(),-2) * -1 as [Second PY],

inyeartodate(TempDate,today(),0) * -1 as [CYTD],

inyeartodate(TempDate,today(),-1) * -1 as [First PYTD],

inyeartodate(TempDate,today(),-2) * -1 as [Second PYTD],

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Ceil(Month(TempDate)/3) as [Quarter Number],

quarterName(TempDate) as [Quarter Name],

yearname(TempDate) & 'Q' & Ceil(Month(TempDate)/3) as [Quarter Year],

inquarter(TempDate,today(),0) * -1 as [CQ], // Current Quarter

inquarter(TempDate,today(),-4) * -1 as [First PQ],

inquarter(TempDate,today(),-8) * -1 as [Second PQ],

inquartertodate(TempDate,today(),0) * -1 as [CQTD],

inquartertodate(TempDate,today(),-4) * -1 as [First PQTD],

inquartertodate(TempDate,today(),-8) * -1 as [Second PQTD],

date(monthstart(TempDate),'MM') as [Month Number],

num(month(TempDate)) as Num_Month,

month(TempDate) as Month,

date(monthstart(TempDate),'MMMM') as [Month Full Name],

monthstart(TempDate) as [Calendar Month Start Date],

monthend(TempDate) as [Calendar Month End Date],

date(monthstart(TempDate), 'MMM-YYYY') as [Month Year],

date(monthstart(TempDate), 'YYYYMM') as [Year Month Num],

week(TempDate) as Week,

week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [Week Year],

week(weekstart(TempDate)) & '-' & Month(TempDate) as [Week Month],

weekDay(TempDate) as [Week Day],

If( TempDate > monthstart(addmonths(today(),-11)) and TempDate <= today(),1) as [Rolling 12]

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Drop Table QuarterNAME;

Table3:

LOAD

RESIDENT MasterCalendar

WHERE not Exists([Date Created]);

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

Re: Master Calendar Not linking correctly to Fact Table

lucasdavis500 — Tue, 08 Nov 2016 19:19:13 GMT

I think I've found the problem:

I took the DATE format off of all of my [Date Created] fields. For the two tables that work, the unformatted date is a WHOLE number, and the dates that aren't working contain decimals. I believe it to be important to not format my date that contains decimals with FLOOR or CEIL because I would like to maintain the hours, minutes, seconds, for grouping purposes..... Do Master Calendar's not work with dates formatted in this manner?

Re: Master Calendar Not linking correctly to Fact Table

MK9885 — Tue, 08 Nov 2016 19:20:46 GMT

I'm not sure if the script I posted will help or not.

Cus I had same situation where I didn't had DATEKey to link MC. So I used below script. But my data was different, I do not know about your data.

Also please check the Date format for Qlikview with your Master Calendar date format and Data Date format.

Thanks.

Re: Master Calendar Not linking correctly to Fact Table

Anonymous — Tue, 08 Nov 2016 19:23:51 GMT

use the floor() function for [date created] and add another local field without the floor() function.

SO you can keep the hours, minutes and seconds...

Re: Master Calendar Not linking correctly to Fact Table

lucasdavis500 — Tue, 08 Nov 2016 19:26:31 GMT

This seems like a drawback of the Master Calendar, if you can't use dates other than strict formatting of integers, then this can cause issues....

Re: Master Calendar Not linking correctly to Fact Table

MK9885 — Tue, 08 Nov 2016 19:29:20 GMT

Use this in Master Calendar

Date(TempDate, 'MM-DD-YYYY - h:mm:ss TT') as [Date Created],

Please verify the Date format with your data.

Re: Master Calendar Not linking correctly to Fact Table

Anonymous — Tue, 08 Nov 2016 19:32:15 GMT

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.

Re: Master Calendar Not linking correctly to Fact Table

Anonymous — Tue, 08 Nov 2016 20:02:16 GMT

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

Re: Master Calendar Not linking correctly to Fact Table

Peter_Cammaert — Tue, 08 Nov 2016 20:15:51 GMT

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

Re: Master Calendar Not linking correctly to Fact Table

MK9885 — Tue, 08 Nov 2016 20:18:16 GMT

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

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