I'm trying to load my "Master Table" into qlikview twice.
Here's my situation. I have 3 tables I've concatenated, and then created a Master Calendar for, based on a data, Date Created, However, these tables also have another date that I would like a Calendar for, and I'm trying to create another Master Table, and Master Calendar, based off of Resolution Date.
My issue: My Resolution Date contains "NULL" Values, since not everything has been "Resolved", so this is messing up my Master Calendar whenever I am trying to find the MIN date, it's finding NULL.
Is there any way around this?
Maybe adding a clause:
IF(ISNOTNULL([Resolution Date], MAX([Resolution Date])) AS MinDate,
not aware that QV has ISNOTNULL
Code below :
QuartersMap:
MAPPING LOAD
ROWNO() AS Month,
'Q' & CEIL (ROWNO()/3) AS Quarter
AUTOGENERATE (12);
Temp:
LOAD
MIN([Resolution Date]) AS MinDate,
MAX([Resolution Date]) 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 [Resolution Date],
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;
