Calculator Transformation

[nwenzel]

I am performing calcs on fields from a DB Table: Units, Cost, and Cost per Unit. I am trying to compare Realized Cost per Unit to the stated Cost per Unit. Some strage data problems (Units = 1.0000000001) are causing some problems.

I am attempting to clean the data by rounding to two points beyond the decimal.

Two questions:

1) Using the Calculator Transformation to Round, I don't seem to be able to specifc 2. Do I have to create a constant, add that constant to each record, then use that field to round to two places ROUND(A,B)?

2) Again, using the Calculator Transformation, how do I round a field without creating a whole new field? Or, if I have to create the new field, is there a better way than the Meta-Data Tab in the Select Transformation to drop the existing field and rename the new field to the existing field?

OK, fine, 1 more question. Would I be better off just using the JavaScript transformation since this seems to be getting a bit complicated?



I am using Kettlev2.5 against an Oracle 10Express database.

Thanks,
Nathan

[sboden]

Sometimes Javascript is the best solution to a problem... but first make a small transformation that shows your problem and attach it (e.g. a Row generator generating your specific values and then rounding in a calculator).

Regards,
Sven

[nwenzel]

I'll post the relevant JavaScript transformation script here.

/*
BEGIN JavaScript transformation
The purpose of this transformation is to get a Realize Rate to eventually compare to the stated rate
Rounding is done to fix some quirky behaviour where values include .000000001
*/

var qtyUnit = QTY_UNIT.getNumber();
var amtBilled = AMT_BILLD.getNumber();
var costPerUnit = AMT_CST_PER_UNIT.getNumber();
var REALIZED_RATE = 0;

qtyUnit = Math.round(100 * qtyUnit)/100;
amtBilled = Math.round(100 * amtBilled)/100;
costPerUnit = Math.round(100 * costPerUnit)/100;

QTY_UNIT.setValue(qtyUnit);
AMT_BILLD.setValue(amtBilled);
AMT_CST_PER_UNIT.setValue(costPerUnit);

if ( qtyUnit == 0 ) {
REALIZED_RATE = 0;
} else {
REALIZED_RATE = amtBilled / qtyUnit;
}
REALIZED_RATE = Math.round(100*REALIZED_RATE) / 10

/* END*/


So, as you can see, I am dividing Amount by Unit to get a Realized Cost per Unit. I ran into some weird bahavior where values that should have been 1.9 were listed as 1.900000001, resulting in a cost per unit that was just slightly off from the stated cost per unit.

I'm happy with the JavaScript listed above. I'd like to know how the Calculator Transformation would work without inserting a new field. But I supposed I could use a Calculator Transformation to create the rounded field and a Select Transformation to remove the non-rounded field. But that doesn't seem very efficient.

If anyone has any suggestions, I'd love to hear them.

Thanks,
Nathan

[sboden]

It's floating point arithmetic... Also open up PDI-FAQ-xxx.pdf in the doc/english directory of Kettle and read "5.8. Calculator ignores result type on division".

The javascript is ok to me... for the calculator/select combo... calculator has a "remove" column at the far right of the dialog which will do what you want.

Regards,
Sven