Handling Nulls in Aggregator

rajeevs · Post by **rajeevs** » Wed Jun 06, 2007 8:20 am

Hello All:

I have a quick question on using the Aggregator stage (Enterprise Edition).

I have an AMT column which is a nullable column and it passes through the Aggregator to determine the sum, based on a set of grouped key columns. When the AMT column is null at the source the data after aggregation shows 0.

Since 0 and Null's are different, i was wondering how to get NULL's at the Output.

Any Suggestions are welcome !!

Thanks All !!

kcbland · Post by **kcbland** » Wed Jun 06, 2007 8:28 am

NULLs are ignored when counting or averaging. The result of a SUM operation will always be a number, never a NULL.

You need to turn zeroes to NULL after aggregation. If your desire is to produce a NULL if all source column values where NULL, you'll have to check for that using a separate aggregation column to count the occurences of NULL.

rajeevs · Post by **rajeevs** » Wed Jun 06, 2007 8:42 am

Thanks Ken !!

I actually did find a solution to the problem !! Within the Aggregator stage there is an option to All Null Output. This by default is set to NULL. When set to True works like a charm !!

kcbland · Post by **kcbland** » Wed Jun 06, 2007 10:40 am

Hey, I just learned something too. There's a lot of little switches here and there, I call it the "Magic Mushroom" approach. The video game Mario Brothers had hidden stars and buttons and mushrooms that if you jumped your character in the right place, you would hit one of these and gain a special power or prize. You only knew where these were by jumping around everywhere to see if something was hidden. It's like that with PX, just click around and see what secret switch or option is available.

Sorry for not being more helpful.

chulett · Post by **chulett** » Wed Jun 06, 2007 10:56 am

Wow... Ken dropping a Mario reference. Who woulda thunk it?