Master calendar discrepancy with original data

diana_ritere — Sat, 16 Nov 2024 16:51:44 GMT

I created master calendar.

However, it has minimal discrepancies with original date fields.

First is export of dates displayed without master calendar.
Second is export of dates created with master calendar.

As you can see, original dataset in some entries has +1 value for the date distinct count.

Why is this happening and how to correct it?

My Master calendar follows such structure:

DatesCal:
Load
Date("Opportunity: Created Date") as DateCol
Resident OriginalTable;

MinMax:
Load
Min(DateCol) as MinDate,
Max(DateCol) as MaxDate
Resident DatesCal;

Let vMinDate = Peek('MinDate',0,'Min_Max');
Let vMaxDate = Peek('MaxDate',0,'Min_Max');

DateID2OppCreateDate:
Mapping
Load
"Opportunity: Opportunity ID" as DateID,
Date("Opportunity: Created Date") as OppCreateDate
Resident OriginalTable;

DateBridge:
Load
"DateID",
Applymap('DateID2OppCreateDate',DateID,Null()) as CanonicalDate,
'Opportunity Created Date' as DateType
Resident FactTable;

MasterCalendar:

Load
Date(DateCol) as CanonicalDate,
//Date(Date#(DateCol, 'DD/MM/YYYY')) as CanonicalDate,
Year(DateCol) as CanonicalYear,
Month(DateCol) as CanonicalMonth,
Week(DateCol) as CanonicalWeek,
'Q' & Ceil(Month(DateCol)/3) as CanonicalQuarter,
Day(DateCol) as CanonicalDay,
WeekDay(DateCol) as CanonicalWeekDay,
Date(MonthStart(DateCol),'MMM YYYY') as CanonicalMonthYear,
Date(DateCol,'MMM YYYY') as CanonicalMonthYear2
; //preceding load

Load
Date($(vMinDate) + IterNo() - 1) as DateCol
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Re: Master calendar discrepancy with original data

diana_ritere — Wed, 12 Jan 2022 16:24:04 GMT

Qlik community,
Any suggestions on how to approach this?

topic Master calendar discrepancy with original data in App Development

Master calendar discrepancy with original data

Re: Master calendar discrepancy with original data