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, 

[ysalvi43]

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; 

[Nagaraju_KCS]

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)