# Thread: Not getting the precision I need from formula

1. Junior Member
Join Date
Aug 2011
Posts
10

## 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. Senior Member
Join Date
Apr 2008
Posts
4,696
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. Junior Member
Join Date
Aug 2011
Posts
10
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. Member
Join Date
Aug 2011
Posts
80
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. Junior Member
Join Date
Aug 2011
Posts
10
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. Senior Member
Join Date
Apr 2008
Posts
1,771
Hi.
I would test the same calculation using a Modified Java script.

Mick

7. Chief Reporting Bug Planter
Join Date
Mar 2003
Posts
8,085
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.

#### 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.