Properly Using Qualify * / Loading Fact Twice

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

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;

Re: Properly Using Qualify * / Loading Fact Twice

Fri, 11 Nov 2016 08:45:06 GMT

Load
max( [Resolution Date]) as MaxDateNoNulls,
min( [Resolution Date]) as MinDateNoNulls
Resident Date//Use Your Date Table
where Len(Trim([Resolution Date]))>0;

Re: Properly Using Qualify * / Loading Fact Twice

jonathandienst — Fri, 11 Nov 2016 08:52:48 GMT

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

Null is skipped over in a Min() function evaluation already, so if Min() returns null, that means there are no non-null values in the source table.

topic Properly Using Qualify * / Loading Fact Twice in QlikView

Properly Using Qualify * / Loading Fact Twice

Re: Properly Using Qualify * / Loading Fact Twice

Re: Properly Using Qualify * / Loading Fact Twice