How to write expression to get values

[#1]

Hi,

I have to use Sum(Commision) in staright table but oit contains some NULL values

how to write expression to skip null values i,e to replace NULL with zero.

Thanks.

[swuehl]

You mean field Commission contain NULLs?

I think what you want is the default behaviour of sum(), what do you observe?

[#3]

Yes filed commision has Null values

so, I need to use ISNULL function . can you please send me the expression how to use ISNULL

[swuehl · Accepted solution]

Well, if I understood correctly, sum(Commission) should be enough, summing all numeric values (and NULL is not a value).

You probably ask for something like

=sum(if (not isnull(Commission), Commission,0))

but I don't think that returns something different than just sum(Commission).

[#5]

Well you could also 'fix' your Nulls in the load script in a similar manner and thereby avoid worrying about it at the expression stage. Thus you could write something along the lines of:

Assuming your load script looks similar to this now:

LOAD

Commission

FROM ...

You could try doing this instead:

LOAD

If(IsNull(Commission,0,Commission) as Commission

FROM ...

// the logic of the above is if the value of Commission field = Null then let its value be 0, otherwise just use whatever value Commission field already has.

[#6]

Thanks and I suppose I should have use

sum(if ( isnull(Commision),0, Commision))

[#7]

Well IMO both swuehl and your logic will work, one is just the reverse logic of the other.

[#8]

Thanks for your answer.

[er_mohit]

in presentation tab below null symbol puts - replace it with 0 then ok

hope it helps