How to override NULL values with existing values

Jonathan Chan gmg

Summary

How to override null value with values from another column in OBIEE Analysis

Content

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

Tom Strudley

Hi Jonathon

What tool are you using for this OTBI or BIP? The reason I ask is that I want to make sure it's not something silly in OTBI like grouping in the display that results in this rather than actual NULLs

 

One option is to create an inner query but this might get quite messy and is only guaranteed if all the values of Item Number relate to Raw Materials one to one. If possible I would really look at the source data and try to rectify for this requirement

 

Thanks

Tom

Jonathan Chan gmg

Hello Tom,

Thanks for reaching out.

I enclosed a screenshot showing the BI platform that I am working on.  In case it doesn't answer your tool question, please let me know what type of functions or interface are you referring to when you say "tool"?

Noted inner query option and you are one step ahead by postulating that item number to raw materials relationship is indeed not always one to one in my scenario.

How would I be able to query up the source data?

Best.

Jon

 

 

 

Tom Strudley

Ok so you're using OTBI so I'm guessing you have a SaaS offering

I will try to recreate this on my side but it's difficult as I'm guessing then that you have no set rule for what you want to set the NULL values to e.g. 100 = Apple because in fact 100 could be Apple, Orange or Banana

Do you know how many NULL values you have? Is it possible to create a data quality report showing all NULLs and sending a report to the transaction owners asking them to update their transactions? Or is it in the thousands?

Jonathan Chan gmg

Actually once you wrote out the e.g. example, I thought about the data again and in fact item number and raw materials relationship is indeed 1 to 1.  Hope this correction help simplifies the work to come up with a solution.

The number of NULL values changes continuously as item number gets added or removed.  Transactions are in the thousands.

To save you time from drawing solutions that do not work under my environment, I have also tried the below EVALUATE function below and it didn't work.  I surmise the reason being I haven't been granted the support to command the evaluate function.

IFNULL(Raw Materials, EVALUATE('LAG(%1, 1) OVER (PARTITION BY %2)' AS varchar2(20), Raw Materials, Product))

FYI the error message contains:

[nQSError: 43113] Message returned from OBIS. EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE. (Doc ID 1922648.1)

Best.