Categories
- All Categories
- 168 Oracle Analytics News
- 34 Oracle Analytics Videos
- 14.8K Oracle Analytics Forums
- 5.8K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 56 Oracle Analytics Trainings
- 13 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 2 Oracle Analytics Industry
- Find Partners
- For Partners
How to override NULL values with existing values

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
Answers
-
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
0 -
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
0 -
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?
0 -
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.
0