Qlik Community

New to Qlik Sense

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

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kalyanamharish
Contributor III
Contributor III

Retrieving only NULL and Blank rows

I want to get/show only ID's which has names has either Blank or NULL in a FilterPane/List Box.

I tried this code : - But, it only give my Blank value 

=Aggr(Only({$<EmployeeName={''}>+<EmployeeName-={"*"}>}EmployeeID),EmployeeID)

 

To save your time

Create table Myemp   --creating table in sql server 
(
EmployeeID int,
EmployeeName Nvarchar(40) Null
)


Insert into Myemp values (111,'Dan')
Insert into Myemp values (222,'Ruby')
Insert into Myemp values (111,'Rolls')
insert into Myemp (EmployeeID) values (888)    --This will enter NULL in Name  
insert into Myemp values (999,'')      --This be enter a blank value in Name

select * from Myemp

 

I am loading this table into my Qlikview/Qlik sense and trying to get list of ID's which has Blank or Null values into a list box

Labels (3)
1 Solution

Accepted Solutions
andoryuu
Creator III
Creator III

You were super close.  This will work for you:

=Aggr(Only({(1-$<EmployeeName={"*"}>)+<EmployeeName={''}>}EmployeeId),EmployeeId)

Note the $ after the 1-.  It's an important distinction before adding back in the blanks after the +

View solution in original post

4 Replies
andoryuu
Creator III
Creator III

You were super close.  This will work for you:

=Aggr(Only({(1-$<EmployeeName={"*"}>)+<EmployeeName={''}>}EmployeeId),EmployeeId)

Note the $ after the 1-.  It's an important distinction before adding back in the blanks after the +

Kushal_Chawda

While loading the data you can create a flag in load script which you can use it in your expression

LOAD EmployeeID,

            if(len(trim(EmployeeName))>0,1,0) as _NullEmpFlag

FROM table;

Now you can use expression like below

aggr(Only({<_NullEmpFlag={1}>}EmployeeID),EmployeeID)

kalyanamharish
Contributor III
Contributor III
Author

Hi both @andoryuu  @Kushal_Chawda ,

Any inputs how we can write the same using Set analysis?

=num(If(SelectDateType = 'Active Today',Count( DISTINCT if((EndDate>= today() OR IsNull(EndDate) or EndDate='') and StartDate<=today(),Assignment_Id)) ,
If(SelectDateType = 'Active on Date Selected',Count(distinct If((EndDate>=vSelectedDate OR IsNull(EndDate) or EndDate='') and StartDate<=vSelectedDate, Assignment_Id)), 
IF(SelectDateType = 'All Historic', Count(distinct Assignment_Id),
If(SelectDateType = 'Assignment Start Date', Count(distinct If((StartDate>=vStartDate  OR IsNull(EndDate) or EndDate='') and StartDate<=vEndDate, Assignment_Id)), 
If(SelectDateType = 'Assignment End Date', Count(distinct If((EndDate>=vStartDate OR IsNull(EndDate) or EndDate='') and EndDate<=vEndDate, Assignment_Id)), 
If(SelectDateType = 'Active Within Date Range',Count(distinct If((EndDate>=vStartDate OR IsNull(EndDate) or EndDate='') and StartDate<=vEndDate, Assignment_Id)) ,
If(SelectDateType = 'Active in Previous Year',Count(distinct If((EndDate>=vStartDatePrev OR IsNull(EndDate) or EndDate='') and StartDate<=vEndDatePrev, Assignment_Id)) ,
If(SelectDateType = 'Active in Current Year',Count(distinct If((EndDate>=vStartDateCurnt OR IsNull(EndDate) or EndDate='') and StartDate<=vEndDateCurnt, Assignment_Id)) ,
)))))))),'###########,###')


 

Kushal_Chawda

Can you please create a separate thread for this as it is a different question?