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

FPonte

Hi Jonathan.

Sorry. I posted a comment (and deleted) as I got into a confusion with Item Number

Not sure if that is possible.

What is the business case for this? Just trying to understand better.

Cheers

Fernando.

Nathan CCC

Here is a brute force method by joining to itself having excluded the nulls in the second query.  

This returns

100 Apple
100 Apple
100 Apple
200 Orange
200 Orange
200 Orange

select all a.name as n, a.value as v1, b.value_2 as v2
from
(
select all 100 as name, null as value from "Sales - CRM Customers"
union all 
select all 100 as name, null as value from "Sales - CRM Customers"
union all 
select all 100 as name, 'Apple' as value from "Sales - CRM Customers"
union all
select all 200 as name, null as value from "Sales - CRM Customers"
union all 
select all 200 as name, null as value from "Sales - CRM Customers"
union all 
select all 200 as name, 'Orange' as value from "Sales - CRM Customers"
) a
left outer join
(
  select all t.name as name_2, t.value as value_2
  from
  (
  select all 100 as name, null as value from "Sales - CRM Customers"
  union all 
  select all 100 as name, null as value from "Sales - CRM Customers"
  union all 
  select all 100 as name, 'Apple' as value from "Sales - CRM Customers"
  union all
  select all 200 as name, null as value from "Sales - CRM Customers"
  union all 
  select all 200 as name, null as value from "Sales - CRM Customers"
  union all 
  select all 200 as name, 'Orange' as value from "Sales - CRM Customers"
  ) t
  where (1=1) and (t.value is not null)
) b
on (a.name=b.name_2) 

 

 

Nathan CCC

BTW COALESCE and OVER PARTITION BY are physical sql functions in a standard oracle database. So you can not expect them to work in logical SQL in the oracle bi metadata repository database (RPD). The logical sql syntax is not the same as standard SQL in most other databases. However as you can see there is a special function EVALUATE to use as a "get out of jail free card" that you may be able to turn on in the configuration so that you can call the physical sql functions from the data source databases in your logical sql. I would caution that i think you should only resort to using evaluate when you have exhausted all other options in normal logical sql. 

FYI the manual...

Oracle® Fusion Middleware
User's Guide for Oracle Business Intelligence Enterprise Edition
11g Release 1 (11.1.1) E10544-08 December 2014
D Logical SQL Reference
https://docs.oracle.com/middleware/11119/biee/BIEUG/appsql.htm#CHDDCFJI

 

Jonathan Chan gmg

Hello Fernando,

No worries.  Tks for reaching out.

The business case is inventory planning.  The goal at this step is to try to consolidate into the same item numbers for products that are identical physically, but are being assigned with different item codes base on case pack quantity.  For example, a product by itself is coded ABCS while its 6 units casepack is coded ABC.

At the moment, I am building an inventory analysis showing product sales record, item codes and inventory-on-hand-quantity for their respective bottle component.  However OBIEE's default BOM analysis does not return any results, thus I am trying to code the component item code into the inventory analysis already having sales data.  The tricky part comes when "Inventory Material" dimension only pulls out component code for components that are from 1 level below.  Using the same aforementioned ABCS product code example, I am able to call out the bottle component item code, B123.  However, for the 6 units casepack code ABC, its 1-level-down component is ABCS.  Thereby, I am attempting to override this constraint by coding my way to show B123 as the component for product ABC.

Best.

Jon

 

 

Jonathan Chan gmg

Thanks for reaching out Nathan and offering the brute force method.

The sample chart is a simplified version of my case, which has dozens of products and they change periodically.  Thereby manually coding each item code might not be applicable in my case.  However I really appreciate your time to type out your solution, which might be a resolution to a future case!

By the way, your profile picture is funny!

 

 

 

Jonathan Chan gmg

The remark is super helpful in enabling me to realize I am working in logical SQL, and why standard SQL didn't work.  Thanks for the link to the Logical SQL manual as well.

Although the below code still didn't work, I hope posting it here will help lead and inspire others to complete their cases:

IFNULL
(
Raw Materials, EVALUATE
(
'LAG(%1, 1) over (PARTITION BY %2)' AS varchar2(20), Raw Materials, Item Number
)
)

By the way, I tested COALESCE and it worked in OBIEE.

Nathan CCC

P.S. On cloud the function EVALUATE is disabled by default in OTBI.

See note on support.oracle.com

Fusion Applications OBIEE - Error using OBIEE Evaluate Function [nQSError: 43113] Message returned from OBIS. EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE. (Doc ID 1922648.1)

The Evaluate function was inactivated by design for Fusion SaaS customers.. While it can be quite powerful, it does open BI up as a security risk, as users may use sql injection via evaluate to get at a lot more data than their security permissions allow

Jonathan Chan gmg

Thanks for the chaser information.

All the best.