Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
vpanchuda
Contributor III
Contributor III

fill the missing values

Hi

Struggling with missing /Null values 

YearMonthIDStartDateEndDateLevel
2018Jan101-01-201802-01-2018Excellent
2018Feb213-02-201814-02-2018Excellent
2018Aug321-03-201822-03-2018Good
2018Sep404-06-201805-06-2018Excellent
2019Jan501-01-201902-01-2019Good
2019Jan613-02-201914-02-2019LOW
2019Feb721-03-201922-03-2019Good
2019Mar804-06-201905-06-2019LOW
2019Sep921-12-201922-12-2019Excellent
2020Feb1001-01-202002-01-2020Excellent
2020Mar1113-02-202014-02-2020LOW
2020May1221-03-202022-03-2020Excellent

 

Script:

 

Fact:
LOAD
// "Year",
// "Month",
ID,
StartDate,
EndDate,
Level
FROM [lib:------
;

startdateCalander:

Load
CalanderDate as StartDate,
day(CalanderDate) as Day,
Week(CalanderDate) as Week,
Month(CalanderDate) as month,
Year(CalanderDate) as Year,
'Q'& Ceil(Month(CalanderDate)/3) as Quarter,
date(monthstart(CalanderDate),'MMM-YYYY')as MonthYear,

;
Load
Date(MinDate+IterNo()-1) as CalanderDate
while MinDate+IterNo()-1<=MaxDate
;

Load
num(Min(FieldValue('StartDate',Recno()))) as MinDate,
num(Max(FieldValue('StartDate',RecNo()))) as MaxDate
Autogenerate FieldValueCount('StartDate')

;

 

Present View  is as below because i believe because of null . User wants me to show the  YEAR field as '0' if there is no level in that particular Year in the below report values Level 'excellent' is available in 2018,2019 and 2020 hence it shows the count but Level'Good' there is no value in 2020 hence don't appear in the table similarly  for level 'LOW' there is no value in 2018 hence wont appear 

 

Capture.JPG

Expected view for Level Good    

YearCount({<Level={'Good'}>}ID)
20181
20192
20200

 

Expected view for Level LOW

 

YearCount({<Level={'LOW'}>}ID)
20180
20192
20201

   

Labels (1)
2 Replies
Sue_Macaluso
Community Manager
Community Manager

@vpanchuda What product are you using? QlikView or Sense? I would like to move this into the correct product forum

Sue Macaluso
vpanchuda
Contributor III
Contributor III
Author

Something went wrong when i posted this :)...its Qlik sense