topic Remove Rows with Nulls and Zeros in Data Quality

Remove Rows with Nulls and Zeros

Rjk — Tue, 04 Nov 2025 19:53:05 GMT

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

Re: Remove Rows with Nulls and Zeros

Amit_Prajapati — Wed, 05 Nov 2025 04:26:33 GMT

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.

Re: Remove Rows with Nulls and Zeros

Rjk — Wed, 05 Nov 2025 08:33:36 GMT

Hi Amit,

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

Re: Remove Rows with Nulls and Zeros

Or — Wed, 05 Nov 2025 09:09:53 GMT

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

Re: Remove Rows with Nulls and Zeros

Rjk — Wed, 05 Nov 2025 11:15:05 GMT

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

Re: Remove Rows with Nulls and Zeros

marcus_sommer — Wed, 05 Nov 2025 15:38:19 GMT

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())

Re: Remove Rows with Nulls and Zeros

Rjk — Thu, 06 Nov 2025 09:38:58 GMT

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

Re: Remove Rows with Nulls and Zeros

ysalvi43 — Thu, 04 Dec 2025 12:53:12 GMT

Hello

With the help of the crosstable you will be able to converted the pivot table, then once you get the unpivoted data, you can clean the data based on your requirements (like if has nulls and zero). Please check the below script.

Draw back: Once the data is unpivoted it is hard to convert it back to pivot table. so I have used the pivot in the front end

Raw data (Excel):

Expected Output (Qlik):

Script:

Table:

CrossTable(Date, Value, 1)

LOAD

"Emp Code",

FROM [lib://AttachedFiles/Null_remove_Excel.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

cross_converted:

load *,

Date(Num#(Date), 'DD-MM-YYYY') as Date_New

resident Table

where not match(Date,'Emp Code');

drop table Table;

NoConcatenate

Final_Table:

load "Emp Code",

'Link' as Linkage

resident cross_converted;

join

load Date_New,

'Link' as Linkage

resident cross_converted;

//drop table cross_converted;

NoConcatenate

Final_1:

load "Emp Code",

"Emp Code" as Null_Emp_Code,

Date_New,

Date_New &'<>'&"Emp Code" as string

resident Final_Table;

//drop table Final_Table;

join

load Date_New &'<>'&"Emp Code" as string,

if(isnull(Value) or len(Value)<1,'Null',Value) as Value_Flag,

Value,

if(isnull(Value),1,0) as Null_Flag

resident cross_converted;

//drop table cross_converted;

NoConcatenate

Null_table:

load "Emp Code" as Emp_Test,

Value_Flag as Value_Test

resident Final_1

where len(Value_Flag)<1;

drop table Final_Table;//group by "Emp Code" ;

//where not match(Value,'-',' ');

drop table cross_converted;

//where match(Null_Flag,0);

//drop table Final_1;

//where "Emp Code" &'<>'& isnull(Value);

NoConcatenate

Final_Temp_Table:

load *

resident Final_1

where not exists(Emp_Test,"Emp Code");

drop table Final_1;

drop table Null_table;

Re: Remove Rows with Nulls and Zeros

Nagaraju_KCS — Thu, 04 Dec 2025 12:59:09 GMT

try this

Create a variable:

LET vAllDates = Count(DISTINCT Work_Date);

Dimension Exp

=Aggr( Count({<Worked_Hours = {">0"}>} DISTINCT Work_Date),Employee_ID)

Measure

= Count(DISTINCT Work_Date)