Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
Qlik community,
Any suggestions on how to approach this?