Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Not getting the precision I need from formula

  1. #1
    Join Date
    Aug 2011
    Posts
    10

    Default Not getting the precision I need from formula

    I'm using a formula step. Both of the numbers are coming in as bignumbers with a mask to multiple decimal places. This is the formula.

    IF ([lastsalesytd] = 0;0.00;(([lastsalesytd] - [lastcostytd]) / [lastsalesytd] ) )

    No matter what I do I can't get the result in either bignumber format or it won't show 2 decimal places on calculation.

    Any thoughts?

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

    Default

    Is this with Preview, or with an output?

    If it's preview, put a text file output where you want to preview and then run your transform. Check what the output in the text file is. Preview doesn't always get number formats right.

  3. #3
    Join Date
    Aug 2011
    Posts
    10

    Default

    It's with output. I saw a bug about that, but it's coming over in mysql as say 21.2 instead of 21.24.

  4. #4

    Default

    do an explain for the table you are querying from.
    see what data type is it.

    Also, perhaps you could insert a select values step and alter its meta-data? Let us know.

  5. #5
    Join Date
    Aug 2011
    Posts
    10

    Default

    I'm pulling data from an openedge/progress database.

    My transformation consist of

    Table input -> Formula -> Join ID -> exec Script

    The formula for computing the field in question is

    IF ([lastsalesytd] = 0;0.00;(([lastsalesytd] - [lastcostytd]) / [lastsalesytd] ) )

    In the table input, if i look directly at database, it says lastsalesytd type real and lastcostytd is type real.

    Pulling the info directly from the database, I get

    lastsalesytd = 10557.80
    lastcostytd = 7196.12

    If I show output fields in spoon from the table, it shows them both as big numbers.

    It gets run through that formula.

    If I look in mysql for that record, I get

    lastsalesytd = 10557.8

    and the math on this | ([lastsalesytd] - [lastcostytd]) / [lastsalesytd] ) | results in

    0.3

    I've tried some different combinations in the formula, tried to change data type, nothing resulted in a precision of 2.

    Any one have an idea?

  6. #6
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Hi.
    I would test the same calculation using a Modified Java script.

    Mick

  7. #7
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Internally our OpenFormula library works with a very high precision (10^-40). We deliver the result back as BigDecimal with that precision and Kettle then transforms that result into whatever data-format you specified as result. So changing the formula has no effect, as the maximum precision is used already - tweak your formula step.
    It has fields for length and precision on the fields-table.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

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.