Hi
Im pretty sure that this has been discussed before, but my searches arent showing any threads.
Im trying to aggregate a flat file column that has 8000+ records. This column is defined as FLOAT. (Business expects data with variable number of digits after decimal point).
The source data is a delimited ASCII file.
The data sample that I have could well be handled by DECIMAL(15,2), but due to the said constraint , i need to define it as FLOAT data. None of the input records has data that contains more than 2 digits after the decimal point.(When seen using an OS Editor or Data browser)
However if I sum up this column using the aggregator stage, the output sum contains 4 digits after the decimal point. ( Again i define the 'sum' column as FLOAT). Now the user group comes back to me and says precision is important, and those 2 extra digits in the sum are a cause for concern. Is there anything that i could do to avoid/bypass this problem?
Aggregator Totals Mismatch
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Search the web for information about why computers can not store FLOAT accurately, and the IEEE standards for representing a FLOAT. When a FLOAT is converted for display, there may be an arbitrary number of digits following the decimal point. It is not a DataStage issue; it's because you have chosen to use FLOAT as a data type. FLOAT can not be stored precisely.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
so you requirement is to get only 2 digits after decimal point.
Either you can use round off function to get nearest values after decimal point
or
write a small basic rountine and call it in tranformer stage.
You need to manipulate column value using basic function.
Use string or field funtion to get exact 2 digits after decimal point.
Concatenate before decimal part and after decimal part.
then convert resultant string into float again.
Either you can use round off function to get nearest values after decimal point
or
write a small basic rountine and call it in tranformer stage.
You need to manipulate column value using basic function.
Use string or field funtion to get exact 2 digits after decimal point.
Concatenate before decimal part and after decimal part.
then convert resultant string into float again.
Re: Aggregator Totals Mismatch
So, precision is important and yet they want a less precise number?rameshrr3 wrote:Now the user group comes back to me and says precision is important, and those 2 extra digits in the sum are a cause for concern.
As noted, round or truncate the result, whichever is appropriate for your user group.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: