Remove Rows with Nulls and Zeros

[Rjk]

Hi Experts

please can you suggest me how to remove the employees with Nulls and Zeros, even if its missing or null for one single day, that employee Id has to removed from my Qlik sense pivot table 

my row is Employee_ID, column is work_date and measure is sum(worked_Hours)

please suggest

[Amit_Prajapati]

Hi @Rjk  , You can use the below expression to mark blank or null as zero.

alt(sum(sales),0)

Alt will return the 0 if there is no values present.

[Rjk]

Hi Amit, 

I want to remove the entire row if there is any  null or zero present in the line 

[Or]

You'd need to modify your measure to return null (or zero) if any instance of it for that row returns null or zero. Something along the lines of:

if(sum(total <Employee> if(Hours>0,1))=count(total <Employee> 1),sum(Hours))

[Rjk]

Hi Or still can't able to as it has few days value has zero for employee Id 58443 and no missing Values and still can't excluded that 

[marcus_sommer]

In general should a check-logic like suggested from @Or be working. But the expression will be depending on the data-set and the data-model, for example are ZERO values possible or even negative ones and/or there are n values per employee and day and/or any missing (NULL) ones ...

Especially NULL is difficult because it couldn't be directly accessed and even if the combination of data-set + data-model + view-requirements enable an indirect query the complexity of such solution could become very ugly. Therefore it's often better to populate the missing values within the data-model.

Assuming that there are no missing records anymore and no negative results and n records per employee and day I could imagine that an approach like the following one may be helpful:

if(min(total Employee aggr(sum(Hours), Employee, Date)), sum(Hours), null())

[Rjk]

Thank you @marcus_sommer, i will try as you said,