Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to remove Negative Values and null values from Expression

Dear Experts,

COLL_CODE LOAN_NO NAME AREASLOANNO_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
Total 105295
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.

13 Replies
tamilarasu
Champion
Champion

May be try,

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

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

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

Anonymous
Not applicable
Author

Maybe you just do this..

Try this..

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

LOAN = alt(IF(STATUS='ACTIVE', LOAN_AMOUNT)*-1, 0)

NO_OF_INS = alt((RANGESUM(AREAS)/INS_AMT)*-1, 0)

sunny_talwar

Although I can this has been answered, but is there a reason you are not using set analysis here?

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

LOAN = Only({<STATUS = {'ACTIVE'}, LOAN_NO {"=RECIEVABLE-RECEIPT_AMT > 0"}>} LOAN_AMOUNT)

NO_OF_INS = I guess this can stay the same (not sure what is going on here

tamilarasu
Champion
Champion

Sunny,    


I agree with all of your point and thought the same when I posted the answer. Believe me, I expected your reply and thought to update my post with set expression. But decided to leave as it is (OP marked the solution as correct).


I am not sure about the last expression and field "INS_AMT". So I have added the "If" Condition, to be on the safer side.

sunny_talwar

Did not mean to say that your solution is incorrect, I am sure it works (and in fact in some case this might be the only solution because of data coming from different tables) But I thought why not look for a way to improve it using set analysis if it is possible

tamilarasu
Champion
Champion

No. I knew that my solution woks fine. I thought to change the expression from "If" to Set analysis like you mentioned. 

Not applicable
Author

Hi , Tamil,

your expression works but results changed as unexpected. I mean not only negative values and null values removed but also changed the positive values unexpectedly.

Thaks for the try.

Priyantha,

Not applicable
Author

Hi, Sunny,

Appreciated your effort but expression you suggested has an error on it . Does not work.

Thks.

Priyantha

Not applicable
Author

Dear Winston,

Thaks for help,

your expression works, but results are same as my existing one.

Rgds,

Priyantha.