2 Replies Latest reply on Nov 11, 2013 10:58 AM by Matt Lutton

# Replacing NULL values with a formula

I have data from commands which are issued to obtain rates. Each command has 8 corresponding rates, which are sometimes NULL. I want a formula to set the NULL values to the maximum rate within the Command ID so there are no NULL values.

For example, take this subset of data within a date partition:

Command Id     Company Code     Rate

[Previous Commands.....]

599533               AL                    127.99

599533               ET                    44.04

599533               ZD                    126.00

599533               ZE                    171.49

599533               ZI                      171.00

599533               ZL                     171.61

599533               ZR                     92.99

599533               ZT                     NULL

[Next Commands....]

Assume that there are going to be many Command IDs within the same date partition. How would I construct a formula that could replace the NULL values with the maximum value within the command ID. So in this case, the NULL value would change to 171.61, while all of the other rates could stay the same. I would call this field [RateNotNull]

I have tried using a calculated field, but it keeps telling me I can't mix non-aggregate and aggregate functions. Any assistance here would be greatly appreciated!

• ###### 1. Re: Replacing NULL values with a formula

Hi Brya,

Sorry for the delay in receiving a response! A calculated field similar to the following should return the desired results:

ifnull(max([Rate]), window_max(max([Rate])))

Hope this helps!

-Tracy

• ###### 2. Re: Replacing NULL values with a formula

Another useful tip is: if you want to replace no data with a zero, you can use the formula:

zn(lookup(sum([Measure]), 0))