aggregator stage & nulls
Moderators: chulett, rschirm, roy
aggregator stage & nulls
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?
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
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
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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
Vincent McBurney
Data Integration Services
www.intramatix.com
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: