topic Re: Nulls and Zeros in SUM function in QlikView

Nulls and Zeros in SUM function

Wed, 09 Mar 2016 11:06:55 GMT

I have a table which contains matters, claimants and reserves. Each matter may have more than one claimant, and each claimant should have a reserve, but they aren't always completed for various reasons. In my LOAD script, I want to produce a table of reserves at matter level, so summing up the reserves for all claimants on each matter. However I really need to be able to distinguish between matters where a zero has been completed for the reserve, and those where it has just not been completed at all (i.e. the reserve is NULL).

I usually do this in SQL but have recently started using Qlik and can't find the answer anywhere the forums. In SQL I would use this syntax:

isnull(convert(varchar(50),cast(sum([Reserve]) as money),-1),'')

So if no reserves have been completed on any given matter, it comes out as blank, whereas if one or more zeros have been completed, it comes out as zero. In qlik even if all reserves on a matter are NULL, the SUM still seems to come out as zero rather than NULL. I have tried first converting the NULLs into 'Missing' but these still seem to add up to zero.

Is this not possible in Qlik?

Any help would be much appreciated.

Re: Nulls and Zeros in SUM function

swuehl — Wed, 09 Mar 2016 17:26:01 GMT

Yes, Qlik handles a SUM of NULL differently than some other DBMS / Tools.

See the reply from Henric here to understand the rationale behind:

Set analysis returns 0, for null records

In Qlik, there are functions to check for specific properties of a field when counting values, see NumericCount(FIELD), NULLCount(FIELD), MissingCount(FIELD), TextCount(FIELD), so maybe something like

If(NumericCount(FIELD), Sum(FIELD), NULL())

can help you getting your requested result.

Hope this helps,

Stefan

Re: Nulls and Zeros in SUM function

Gysbert_Wassenaar — Wed, 09 Mar 2016 18:56:37 GMT

If each claimant can have only one reserve then you can try only(reserve) as expression since there won't be a need to sum the reserve values. In a straight table you can set the Total Mode of the expression to Sum of Rows so you will get a total.

Re: Nulls and Zeros in SUM function

MarcoWedel — Mon, 20 Nov 2023 14:17:37 GMT

moved to new thread