Summing Nulls

Good question from Remc0 on the forum recently.

When the following in a group:
<?sum(current-group()/_saw_8_[.!=鋳])?>

It returns a 0 when all values are null. But 0 means smaller then 0,5 and null means it is not there. Now my question is: how can I sum the values and make a different between 0 and null?

Its a good question and there is a reply to use the updateable variables - I have to admit an aversion to updateable variables - they are expensive and are not a feature of native XSL - there are other ways around using the 'variables'


Remco's basic problem is that the code above that checks and eliminates null values in a sum function returns a zero value when all values are null. For example with the following XML.

<AMOUNTS>
<AMOUNT type="INV"></AMOUNT>
<AMOUNT type="INV"></AMOUNT>
<AMOUNT type="CM"></AMOUNT>
<AMOUNT type="CM">100</AMOUNT>
</AMOUNTS>


The sum of the amounts of the type 'CM' using the XPATH null elimination:


<?sum(AMOUNT[@type='CM' and .!=""])?>


as an aside, notice we're combining XPATH expressions with an 'and' - we want to sum all AMOUNT values where the type attribute is CM and the values are not 'null'. In this case we get '100' returned - thats good. If we sum the INV types:


<?sum(AMOUNT[@type='INV' and .!=""])?>


we get a zero returned - which as Remco notes is not actually correct - well not for his requirement, he wants to see an empty string.


My approach was to use our inline 'if' statement.


<?xdoxslt:ifelse(sum(AMOUNT[@type='CM' and .!=""]) = 0,'',sum(AMOUNT[@type='CM' and .!=""]))?>


The if statement tests the returned value and if its a zero ie the XML values are null then an empty string (two single quotes) is inserted otherwise it returns the calculated amount. This is fine if all the values are null but if the returned sum value happens to be '0' then an empty string will be returned.


In the interest of audience participation, here's a challenge. Can anyone come up with the XPATH necessary to return the real zero values and handle the null case too?

Comments:

Why don't you replace by

Posted by Tilde on September 10, 2009 at 10:48 PM MDT #

Great trick Master Dexter, This tip helped to resolve a problem, thanks very much.

Posted by Hugo on August 17, 2010 at 09:06 AM MDT #

Great...Solved the problem

Posted by guest on February 24, 2013 at 10:55 PM MST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today