Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How to override NULL value

Received Response
43
Views
3
Comments
Jonathan Chan gmg
Jonathan Chan gmg Rank 5 - Community Champion
edited Aug 13, 2024 5:19PM in Oracle Analytics Cloud and Server

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.

Data Example chart.png

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod 
    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.

  • Jonathan Chan gmg
    Jonathan Chan gmg Rank 5 - Community Champion

    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'

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

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