topic Re: Exclude Zeros in QlikView

Exclude Zeros

Fri, 26 Jul 2013 06:20:29 GMT

Hi All,

Anybody knows how to exclude zero values for result of column(2)- column(1) which is 0.0 and -0.0 in straight or pivot table.

thanks in advance

Re: Exclude Zeros

Fri, 26 Jul 2013 06:22:37 GMT

in presentation tab

Null symbol and Missing symbol option

see attachement

Re: Exclude Zeros

Fri, 26 Jul 2013 06:31:03 GMT

if(column(2)- column(1)=0.0 or column(2)- column(1)='-0.0',' ',column(2)- column(1))

in presentation tab--

supress null value option already checked.

make sure if you have more than one expression and if in a particular row in all column have null value then it automaticall supressed, if any one column not null value then it doesn't supressed.

also in dimension select field and then check maually supress null value option

SEE ATTACHEMENT

EXAMPLE--

IN SCRIPT--

LOAD * Inline [

id, A,B

1,2.2,2.3

2,1.7,1.7

3,2.9,3.6

4,4.5,4.5

5,8.6,5.5

];

THEN IN PIVOT-

DIMENSION1- id

DIMENSION2- A

DIMENSION3- B

EXPRESSION- A-B

Re: Exclude Zeros

Fri, 26 Jul 2013 06:39:16 GMT

option is already selected, and suppress null is also selected,

Exp 1:Sum(Balance) and Exp 2:Sum(Amount)

Exp 3: Sum(Amount)- Sum(Balance)

if that is 0.0 and -0.0 then how to exclude them in front end or in back end..

Re: Exclude Zeros

Fri, 26 Jul 2013 06:44:28 GMT

option is already selected, and suppress null is also selected,

Exp 1:Sum(Balance) and Exp 2:Sum(Amount)

Exp 3: Sum(Amount)- Sum(Balance)

if that is 0.0 and -0.0 then how to exclude them in front end or in back end..

Re: Exclude Zeros

Fri, 26 Jul 2013 06:44:47 GMT

Hi There,

option is already selected, and suppress null is also selected,

Exp 1:Sum(Balance) and Exp 2:Sum(Amount)

Exp 3: Sum(Amount)- Sum(Balance)

if that is 0.0 and -0.0 then how to exclude them in front end or in back end..

Re: Exclude Zeros

Fri, 26 Jul 2013 06:45:20 GMT

Hi There,

option is already selected, and suppress null is also selected,

Exp 1:Sum(Balance) and Exp 2:Sum(Amount)

Exp 3: Sum(Amount)- Sum(Balance)

if that is 0.0 and -0.0 then how to exclude them in front end or in back end..

Re: Exclude Zeros

Fri, 26 Jul 2013 06:45:42 GMT

Hi There,

option is already selected, and suppress null is also selected,

Exp 1:Sum(Balance) and Exp 2:Sum(Amount)

Exp 3: Sum(Amount)- Sum(Balance)

if that is 0.0 and -0.0 then how to exclude them in front end or in back end..

Re: Exclude Zeros

jonathandienst — Fri, 26 Jul 2013 07:02:15 GMT

If you want to suppress the lines where Sum(Amount) and Sum(Balance) are equal, then use these expressions:

Exp1: If(Round(Sum(Balance) - Sum(Amount)) <> 0, Sum(Balance), 0)

Exp2: If(Round(Sum(Balance) - Sum(Amount)) <> 0, Sum(Amount), 0)

Exp3: Round(Sum(Balance) - Sum(Amount))

And ensure that the Suppress Zero Values option is checked.

Jonathan

Re: Exclude Zeros

Mon, 24 Feb 2014 16:49:43 GMT

Hi Jonathan,

I have the following code which gives me CMP_Date and Pre.

Wrkflow:
LOAD   Process_Code ,
Application_id   as   Application_Id ,
Workitem_Id ,
Activity_Code ,
Timestamp ( Txn_Date ) as   Txn_Date ,
State_Code ,
Assigned_User_Id ,
Assigned_Dept_Id ,
Actioned_User_Id ,
applymap ( 'Trays' , Activity_Code , null ()) as   Tray ,
applymap ( 'Trays' , Activity_Code , null ())& State_Code   as   Link ,
if ( State_Code = 'CMP' , timestamp ( Txn_Date ), null ()) as   [CMP_Date]

FROM

( txt , codepage is 1252 , embedded labels , delimiter is '\t' , msq ) ;

Wrkflow2:
Load   * , 1
Resident   Wrkflow
Order   by   Application_Id , CMP_Date , Tray , State_Code;
DROP   Table   Wrkflow;

Wrkflow3:
Load   * ,
CMP_Date - if ( State_Code = 'CMP' ,( Previous ( CMP_Date )), '' ) as   diff ,
networkdays (( Previous ( CMP_Date )), CMP_Date ) as   diff2 ,

if ( State_Code = 'CMP' ,( Previous ( CMP_Date )), '' ) as   pre
Resident   Wrkflow2;
DROP   Table   Wrkflow2;

I am using CMP_Date and Pre in the following expression to get working hours and it is working well if we see application by application but when it it looked by product type (which is Average) is not correct because there are zeros in each tray and the average is decreasing. How can I ignore zeros in average .

sum({<Tray={'NAPS CJA Automatic Check'}>}fabs(interval(((Networkdays(Date(pre,'DD/MM/YYYY hh:mm:ss'),Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')

if(frac(date(pre))<num('$(vQuitTime)'),if(frac(date(pre))>num('$(vStartTime)'),Date#(date(floor(pre),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date(pre,'DD/MM/YYYY hh:mm:ss'),Date#(date(floor(pre),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date#(date(floor(pre),'DD/MM/YYYY') & '$(vStartTime)' ,'DD/MM/YYYYhh:mm:ss')),0)

if(frac(date(CMP_Date))>num('$(vStartTime)'),if(frac(date(CMP_Date))<num('$(vQuitTime)'),(Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')-Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vQuitTime)','DD/MM/YYYYhh:mm:ss')-Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),0), 'hh:mm:ss'))*24)/count(distinct Application_Id)+

sum({<Tray={'NAPS Contact Customer'}>}fabs(interval(((Networkdays(Date(pre,'DD/MM/YYYY hh:mm:ss'),Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')

sum({<Tray={'NAPS Print Documents'}>}fabs(interval(((Networkdays(Date(pre,'DD/MM/YYYY hh:mm:ss'),Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')