Toad Data Point - Transform and Cleanse - Rounding Issue

Toad Data Point
1

When using the Transform and Cleanse functionality within Toad Data Point the result of the calculation is incorrectly rounding the result to the nearest whole number. Due to this calculations are not working correctly within the transform. Please see the examples below of the issue. This only appears to affect calculations using the built in functions, not the SQL Transform functions.

Formula with "correct" output (not rounded)

ToadWorkingFormula
ToadWorkingFormula.jpg963×163 19.4 KB

Inputs into Formula
ToadInput1 ToadInput2

Correct Output
ToadCorrectOutput

Here is the output in error and the associated formula

Formula not working correctly

ToadErrorFormula
ToadErrorFormula.jpg985×123 17.5 KB

Incorrect Output, note the values are rounded
ToadErrorOutput

These screenshots were taken from the same Transform and Cleanse session with both columns included. Inputs are the same for both formulas

Any assistance would be appreciated.

2

Hi Richard,
I'll try to reproduce it.

3

Dan, any luck in reproducting this issue? Just curious as we deal with financial information to the penny so rounding to the nearest whole number on a percentage for a calculation is a very big deal for us.

4

Hi Richard,

Daniel just asked me for assist on this example as he is not there for few days.

It looks like that IF statement in forma just rounding your values because result is INT. If you want to use correct number you have to change your ELSE statement from 0 to 0.0 so you will get result in DOUBLE.

if (Nvl( [COST_TYPE_CODE] , 98)==2 AND Nvl( [POOL_TYPE_NO] , 98), [POOL_RT], 0.0)

5

Thank you Filip, I will give that a try and will report back.

6

Filip, that answer worked and returned a non-rounded result. I appreciate your assistance.