aggregator stage & nulls

[datastage]

I'm using the average function on a column in an aggregator stage, and the input data contains nulls. In a true mathematical sense the returned average should be null, but the tool does what is more logical & useful and still computes an average. But the question is does it ignore nulls or does it treat them as zeros and as part of the population to compute the average from? IE, if I have 10 rows of data with 2 nulls, does it sum the data and divide by 10 or 8?

[msigal]

I ran a 5 record test file with a null in it and it did not count the Null row in the average, it divided by 4 not 5. I used a sequential stage for input. I expect you may get different results depending on how you are reading the null. I did get a warning, stating it couldn't convert my null to the numeric format I had specified in the table definition.

I hope this helps.

Myles

Myles Sigal
Technical Analyst
The MEDSTAT Group
777 E. Eisenhower - 435B
Ann Arbor, MI 48108

myles.sigal@medstat.com
734-913-3466

[rasi]

Hi,

It all depends what you want to do. If you want only the non-nulls to be considered in the average then it is the default. If you want the null to be counted as well then use the transformation to substitute the null value to 0 and send it to the aggregretor stage.

Thanks
Rasi

[vmcburney]

That's good advice. Automatically treating all nulls as 0s could cause problems. Let's say you are averaging restaurant ratings out of 10 and several people abstained from providing a score. Turning their nulls into zeros could make a decent restaurant look like McDonalds.

Vincent McBurney
Data Integration Services
www.intramatix.com

[ray.wurlod]

Be specific in a preceding Transformer stage exactly how you want to treat NULL values.
Convert them to zero if that's what you want.
Constrain the output not to pass NULL values if that's what you want.


Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518