topic Re: How to identify and empty cell in script?? in QlikView

How to identify and empty/null cell in script??

Fri, 14 Aug 2015 18:52:01 GMT

Hello!

Well I have this problem I am doing a distribution of general cost based on the actual cost of products, that means I calculate a distribution percentage in every data entry, and then I multiply this percentage by the amount to distribute.

It is simple, but my problem comes when I add up the actual cost of the products to the resulting on the already explained expression, my idea is that I must use an "if" function, to tell my script to not add up the result to the actual cost, when the result is in fact an empty cell, but I do not know how to say that, something like:

IF( result='blank',actualcost,actualcost+result) as finaldist

'blank' does not exists apparently, and that is my problem, the table looks like this:

product	actualcost	generalcost	perecentage	result	finaldist
A	100	500	0.125	62.5	562.5
B	200	500	0.25	50	250
C	400	-	0.5	-	-
D	100	500	0.125	62.5	162.5

THANK YOU!

Re: How to identify and empty cell in script??

maxgro — Fri, 14 Aug 2015 19:00:30 GMT

there is a isnull() function

or you can try with

IF( len(trim(result))=0,actualcost,actualcost+result) as finaldist

another one

rangesum(actualcost, result) as finaldist

I suppose all your fields are in one table

Re: How to identify and empty cell in script??

Fri, 14 Aug 2015 19:21:33 GMT

Yep, all my fields are inside one single table. And it actually worked, thank you this other one also makes the trick:

If(isnull(PORCIONALL2),CTOAGGR2, PORCIONALL2+CTOAGGR2) as DISTFINALALLPROD