Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

How to remove negative and null values from expression

Dear Tamil,

Here is the sample file,

Dear Experts,

COLL_CODELOAN_NONAMEAREASLOANNO_OF_INS
CHEQUERL0068A.M.C.L.ATTANAYAKE03000000
CL001RL0013D.A.DAGLAS2250010000018
RL0035K.LIYANARACHCHI100002000040
RL0044P.D.GAMAGE224002000009
RL0050M.A.K.I.FERNANDO2975015000015
RL0051D.M.R.S.THENNAKOON135002000005
RL0054R.G.PREMACHANDRA25002000001
RL0067A.M.R.BANDARA325250001
RL0069K.P.ARIYASENA0500000
RL0071S.A.U.PALITHA20300000
RL0073J.M.DUSHANTHA0500000
RL0074E.G.D.M.K.PRADEEP-47525000-1
RL0075T.G.A.LASANTHIKA01000000
RL0076K.U.SARANAPALA25002000001
RL0077G.V.C.JAYATHILAKA-35050000-1
RL0078N.M.P.NAWARATHNE-35050000-1
RL0082D.M.R.S.THENNAKOON3900700004
RL0089L.S.GAJANAYAKA-675150000-0
RL0092J.C.KUMARI01000000
RL0093K.R.SILVA-250150000-0
RL0100V.SHIVAKUMAR0250000
Total105295
CL002RL0081A.R.THILAKARATHNE01000000
RL0085N.SIVARAJAH575500001
RL0086M.KALEIRAJAH390300001
RL0090D.R.T.SABRIN-150060000-2
RL0095S.H.M.SHAKEER-7525000-0
RL0099J.M.C.P.K.JAYATHILAKA01000000
RL0101R.M.SARATH190150001
Total-420


My pivot table shown above.

AREA = SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT))))

LOAN = IF(STATUS='ACTIVE', LOAN_AMOUNT)

NO_OF_INS = RANGESUM(AREAS)/INS_AMT

I want to remove the negative and null values from the resulted table. so How can I modify my expressions to do the same?

Thanks in Advance.

Priyantha.

1 Solution

Accepted Solutions
Highlighted

Here try these now:

1)

Sum(Aggr(

if(SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))) > 0 and

IF(STATUS='ACTIVE', LOAN_AMOUNT) > 0 and

RANGESUM(SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))))/INS_AMT > 0,

SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))),null()), COLL_CODE, LOAN_NO, NAME))

2)

Sum(Aggr(if(SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))) > 0 and

IF(STATUS='ACTIVE', LOAN_AMOUNT) > 0 and

RANGESUM(SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))))/INS_AMT > 0,

IF(STATUS='ACTIVE', LOAN_AMOUNT),null()), COLL_CODE, LOAN_NO, NAME))

3)

Sum(Aggr(if(SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))) > 0 and

IF(STATUS='ACTIVE', LOAN_AMOUNT) > 0 and

RANGESUM(SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))))/INS_AMT > 0,

RANGESUM(SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))))/INS_AMT,null()), COLL_CODE, LOAN_NO, NAME))

View solution in original post

28 Replies
Highlighted
Specialist
Specialist

Hi,

Try this one

AREA =if( SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT))))>0,SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))))

LOAN =if( IF(STATUS='ACTIVE', LOAN_AMOUNT)>0, IF(STATUS='ACTIVE', LOAN_AMOUNT)

NO_OF_INS =if( RANGESUM(AREAS)/INS_AMT>0,RANGESUM(AREAS)/INS_AMT)

Regards,

Joshua.

Highlighted
Master III
Master III

There are number of ways

* handling them at back end that will surely provide you a best response time and less memory utilization for the object (pivot table)

* the next way is handling such values in the expression itself like you have expression as Total Cost - Expense

  writing it as =if(Total Cost >= Expense,Total Cost - Expense)

  Or even handling it by =if(left(Total Cost - Expense)='-',null() ,Total Cost - Expense)

Highlighted

try like this

AREA =

if(SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT))))>0,SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))),null())

LOAN =

if(IF(STATUS='ACTIVE', LOAN_AMOUNT)>0,IF(STATUS='ACTIVE', LOAN_AMOUNT),null())

NO_OF_INS =

if(RANGESUM(AREAS)/INS_AMT>0,RANGESUM(AREAS)/INS_AMT,null())

Go to presentation tab and check the suppress null value  option

Highlighted

Hi Priyantha,

Check the attachment.

Highlighted
Specialist III
Specialist III

hi,

try to use set expressions, by default set expression removes Null values

=sum({<STATUS={'ACTIVE'},FLAG={'A','R'}>} RECIEVABLE-RECEIPT_AMT)

=Sum({<STATUS={'ACTIVE'}>} LOAN_AMOUNT)


use this calculated Dimension in place of NAME dimension to remove -ve values

=if(  (aggr(sum({<STATUS={'ACTIVE'},FLAG={'A','R'}>}RECIEVABLE-RECEIPT_AMT),NAME,LOAN_NO,COLL_CODE))>0,NAME)

Highlighted
Not applicable

Dear Joshua,

Your expression works as expected but i cant get the correct subtotal.

Thkx for the attempt.

Priyantha.

Highlighted
Not applicable

Hi, Avinash,

Your expression works as expected but i cant get the correct subtotal.

Thkx for the attempt.

Priyantha.

Highlighted
Partner
Partner

Hi

Instead change all expression ,we can change  like

replace LOAN_NO by   below expression

Aggr(Only( {<LOAN_NO={"=SUM({<STATUS={'ACTIVE'}, FLAG={'A','R'} >}(RECIEVABLE-RECEIPT_AMT))>0"}>}

LOAN_NO),LOAN_NO)

and  check the suppress null values for the dimensions

Highlighted
Not applicable

Dear Tamil,

I have only PE version, please post on the wall,

Thnx,

Priyantha.