How to sum values including nulls using Groovy?

    This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

    • How to sum values including nulls using Groovy?

      I am using a Groovy formula to add a set of fields. It does not provide a total if one of the fields has a null value.

      I get these messages depending what I am trying:

      For: AvgRevperMember=_input1/(_input2+_input3+_input4+_input5+_input6+_input7+_input8)

      I get: Warning: Failure in function Average Revenue per Member: Unable to execute script: java.lang.NullPointerException

      or

      For: return (_input1+_input2) / (_input3+_input4+_input5)

      I get: Warning: Failure in function Average Hospital Expense per Hospital Day: Unable to execute script: Cannot invoke method plus() on null object


      How can I sum values including nulls in Groovy or how can I replace null values with zero?
      Thanks

      Palo Version 3.0 Build 3969
      Palo ETL Server Version: 3.0.943
      Palo ETL Web Version 3.0.143
      Windows XP Professional 2002 SP3
      Excel 2002 (10.2614.2625)
    • Can I use SQL in a FieldTransform? I have a set of fields I need to convert. I don't see it mentioned in the field transform section of the documentation.

      Does anyone know the equivalent statement for Groovy?
      Thanks

      Palo Version 3.0 Build 3969
      Palo ETL Server Version: 3.0.943
      Palo ETL Web Version 3.0.143
      Windows XP Professional 2002 SP3
      Excel 2002 (10.2614.2625)
    • The data is from a transform. The null values are created in the transform (a denormalization), so it is not possible to deal with them earlier.
      Thanks

      Palo Version 3.0 Build 3969
      Palo ETL Server Version: 3.0.943
      Palo ETL Web Version 3.0.143
      Windows XP Professional 2002 SP3
      Excel 2002 (10.2614.2625)
    • then you should transform these null values into a countable value.

      Perhaps with java transformer you can test null values.

      If there is no possibility to use a given transformer you should create one.
      As far I know this is not documented in manaul.
      sivgin

      The post was edited 1 time, last by sivgin ().

    • You could check for Null values in the Groovy-Script:
      if (_input1==null)
      ....
      or use the function Map with a Mapping definition for empty string -> 0

      In general, no transform should generate any Null-values. This is a bug for Denormalisation which will be changed in the next ETL-Release. In general you should define an Aggregation for the Measures of Denormalisation so that the rows are packed together. With this, also the Null-values disappear.