Retrieving only NULL and Blank rows

[kalyanamharish]

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

[andoryuu · Accepted solution]

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]

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?