Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: "Minimum" and null values

  1. #1

    Default "Minimum" and null values

    Hi all,

    the Min aggregation function, in Group by step, returns null is there is a null value among those aggregated. If I ask for the Min "value" I expect to get the minimum among the values, not including null values. Is there a way to avoid it? I can't use "First non null value" each time, otherwise I couldn't aggregate more fields in the same grouping (grouped column should be sorted first). Or should I submit a JIRA case asking for a "Minimum (not null)" aggregation function?

    Thanks,

    Iuri

    P.S. See attachment for examples
    Attached Files Attached Files
    Last edited by biuri; 06-30-2009 at 10:28 AM.

  2. #2

    Default What does SQL say?

    Hi Iuri

    My guess is that you would still get null as it is the min value, but I have not tested this.

    If you do something similar in SQL on a db table and get the same result then I would guess you'll have to filter out the nulls before you want to determine the null value.

    Hope this helps.

    Regards
    Paul

  3. #3

    Default

    Hi,

    thank you for the reply. Actually it is not possible for my scenario, because filtering rows with null values in one field would cause deleting rows with non-null values in other fields (that I want to group). Other ideas?

    Iuri
    Last edited by biuri; 06-30-2009 at 02:54 PM.

  4. #4

    Default

    No one out there?.. I'm going for a JIRA then.

  5. #5
    Join Date
    Jan 2014
    Posts
    3

    Default

    Facing the same problem here.

    Group by step should be considering null values when looking for the minimum value, or at least it would be nice to have an option either to consider them or not.

  6. #6
    Join Date
    May 2016
    Posts
    282

    Default

    You could implement a previous step changing the null value to a value that would be max, for example, ZZZ if it's a string column, or a string of 9 large enough if it's a number value, so it never returns the null value if there's another value in the aggregating group, and then perform the opposite operation to get the null value again in case that is the min value in your group.
    Regards
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

  7. #7
    Join Date
    Aug 2016
    Posts
    290

    Default

    Quote Originally Posted by STIG View Post
    Facing the same problem here.

    Group by step should be considering null values when looking for the minimum value, or at least it would be nice to have an option either to consider them or not.
    Wasn't the whole topic about null being considered when looking for minimum value, but extra functionality was requested for an option to exclude null when aggregating minimum?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.