Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Properly Using Qualify * / Loading Fact Twice

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;

2 Replies
Highlighted
Not applicable

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

Highlighted

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein