Hitachi Vantara Pentaho Community Forums
Results 1 to 15 of 15

Thread: How to adjust floating point precision of aggregating SUM using "group by" step?

  1. #1

    Question How to adjust floating point precision of aggregating SUM using "group by" step?

    Hi! I am currently setting up a CSV-importing involving the aggregation of a field typed Number. But the aggregated sum only serves a single digit behind the floating point. I need at least two. The following example is a simplified example illustrating the issue.

    the transformation:
    Name:  trafo.png
Views: 330
Size:  5.1 KB

    I choose Number using a precision of 3 figures behind the comma.
    Name:  data_grid_settings.jpg
Views: 328
Size:  11.8 KB

    Name:  data_grid_preview.png
Views: 326
Size:  4.7 KB

    Name:  group_by_settings.png
Views: 402
Size:  10.8 KB

    But the result is rounded to 1 digit behind the comma.
    Name:  group_by_preview.png
Views: 319
Size:  4.6 KB

    How can I increase the precision?

    Thanks

    Raffael
    Last edited by joyofdata; 06-06-2013 at 08:12 AM.

  2. #2

    Default

    i duplicated your work EXACTLY and mine returns sum_x as 7444

    I am using PDI 4.4.1. FYI. What version are you using?

  3. #3

    Default

    4.4.0. ... so maybe? I will download the new version and come back with the result!

    If you could please check the attached transformation. That would be cool.
    Attached Files Attached Files
    Last edited by joyofdata; 06-06-2013 at 09:03 AM.

  4. #4

    Default

    I can't find a download link for version 4.4.1. kettle.pentaho.org only leads me to sourceforge site offering 4.4.0 ... !?

  5. #5

    Default

    I think 4.4.1 is part of Pentaho Enterprise Edition 4.8 (not community edition).

    We paying customers get the good stuff before you freeloaders! ;-)

    Name:  pdi-version.jpg
Views: 342
Size:  26.6 KB

  6. #6

    Default

    But did you check my attached transformation? Does it still work for you? And you write that you get "7444" - that's a typo, right? You got "7,444".

    Because what could be is this ... the two operands "3,123" and "4,321" are interpreted as integers because of your language settings - so "," is not as in my case interpreted as a decimal separator but a thousand separator.

    And eventually you get 7444 from it and your Kettle version didn't even bother with floating point precision ... (WTF)

    So maybe you could try my transformation once as is and a second time using "." instead of "," so it is interpreted as a float.

    Thanks

  7. #7

    Default

    I made it work by using "." instead of "," in the CSV. I find it confusing but sticking to US number formatting will be the easiest ond most reasonable solution I guess.

  8. #8
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by joyofdata View Post
    How can I increase the precision?
    Just add a Select Values, select the Meta-Data page and add your aggregate field with a format string "0.0000".
    You can always see the internally used format of a field when using action "Show output fields" from a step context menu.
    sum_x is a generated field, so Kettle added a default format for you, hence the single decimal place.
    So long, and thanks for all the fish.

  9. #9

    Default

    I will try that as well, thanks

  10. #10
    Join Date
    Feb 2011
    Posts
    840

    Default

    I may be saying BS here, but I remember someone telling me that, despite what pentaho SHOWS, it stores a full value, so wouldn't it just be the case of adding a "select values" after the "group by", with the format you want?

    Also, one hard lesson I've learned was: ALWAYS configure Decimals and Grouping characters. ALWAYS. I can see from your screenshot on the first post that you've left that blank.

    on a side note...
    PDI 4.4.2 nightly build is available on the CI - http://ci.pentaho.com/view/Data%20In...ob/Kettle-4.4/
    just to remind, I quote the CI...
    WARNING: CI builds are by nature bleeding edge, and are not considered to be stable. If you require an official community release of one of our products, please see our SourceForge download page here. Please report any bugs against builds from SourceForge and not CI builds.
    and sfrattura, don't be a troll. no need for that right?
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  11. #11
    Join Date
    Feb 2011
    Posts
    840

    Default

    damn marabu, you beat me to it while I was typing lol
    I'm trying to remember if it was you who taught me that. Either you, Matt, gutlez or Slawo. =p
    Quote Originally Posted by marabu View Post
    Just add a Select Values, select the Meta-Data page and add your aggregate field with a format string "0.0000".
    You can always see the internally used format of a field when using action "Show output fields" from a step context menu.
    sum_x is a generated field, so Kettle added a default format for you, hence the single decimal place.
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  12. #12
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by joao.ciocca View Post
    damn marabu, you beat me to it while I was typing lol
    I'm trying to remember if it was you who taught me that. Either you, Matt, gutlez or Slawo. =p
    Or all of us independently!

    I tell new users of that aspect quite regularly. The other thing I suggest to new users is to replace what they want to preview with a Text Output, and format the columns appropriately in the output. That will show you what you're really getting, not what you're seeing.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  13. #13
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by joao.ciocca View Post
    I'm trying to remember if it was you who taught me that.
    No way, I'm no teacher, I'm a student, quite a seasoned student, but nevertheless I'm here to learn.
    So long, and thanks for all the fish.

  14. #14
    Join Date
    Feb 2011
    Posts
    840

    Default

    cut that crap, marabu =p you've taught me a lot already. The way I see it, we are all teachers and students in here, always learning something new.

    Also, the best teacher isn't the one who thinks he knows it all, but the one who's always open to learn a new thing - even from his own students
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  15. #15
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by joao.ciocca View Post
    The way I see it, we are all teachers and students in here, always learning something new.
    I'd agree with that, and Marabu has taught me more than a few things too!
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

Tags for this Thread

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.