How to override NULL value

Question

When building an analysis in OBIEE, what would be the code to enter into the "Column Formula" box in "New Calculated Measure" if I want to create a new column where null values are override with existing values?

For example, in the enclosed screenshot I have the analysis shown on the left.  My goal is to fill the Null values with the first/only NOT NULL values in "Raw Materials" column per each product i.e A, B and C in Product column.  Result per dataset shown to the right.

The following 3 sets of code did not work:

CODE 1:

IFNULL(Raw Materials,

First_value(Raw Materials) OVER (PARTITION BY Product))

CODE 2:

IFNULL(Raw Materials,

First_value(Raw Materials) OVER (PARTITION BY Product RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))

CODE 3:

COALESCE(lag(Raw Materials ignore null) over (partition by Product), Raw Materials)

Version: Oracle Business Intelligence 11.1.1.9.0

Thank you.

Jerry Casey · Answer

How about: ifnull(Raw Materials, MAX(Raw Materials by Item Number)) ?

Jonathan Chan gmg · Answer

Thanks for reaching out Gianni.

Good point.  How do I determine whether the cells are empty or NULL?

The following properties are observed from empty cells in my OBIEE analysis, which the chart is mimicking:

1) The null cells are result of parsing strings from another other columns/attribute.

2) When I ran IFNULL(Raw Materials, '1'), the empty cells are filled with '1'

Gianni Ceresa · Answer

Jonathan Chan gmg wrote:My goal is to fill the Null values with the first/only NOT NULL values in "Raw Materials" column per each product i.e A, B and C in Product column.

Do those lines really have NULL has a value? NULL is very different than empty, NULL being a value by itself while if empty there is just nothing.