Return First Non Null value

Question

HI All

I am working on a report and I need to return planner code at item level.

Typically planner codes are set at Item - Org combinations.

But I am creating one report aggregated at item level.

There are few orgs where there is no planner code. But OBIEE should return first Non Null Planner code.

Can this be done?

Please see example below.

Item - Org - Planner Code

1234 - ABC - PLN1

1234 - DEF - PLAN2

1234 - GHI - NULL

1234 - JKL - NULL

Now in the report, we should display ANY of the following values -

Item - Planner Code

1234 - PLN1

1234 - PLAN2

Thanks in advance for any input on how to achieve this.

Regards

Gianni Ceresa · Answer

Maybe this is helpful?

Because for now the thread is still This question is Not Answered.

So would be helpful for other users if you look again on how to mark which will also close the thread.

A Md Y · Answer

CORRECT ANSWER !!!

Thanks

Somehow I was unable to flag this as CORRECT ANSWER.

Gianni Ceresa · Answer

Hi,

First thing first: a report as in BI Publisher report or an analysis in OBIEE Answers? The name defines the tool, the tool drives the answer you look for ... ( (c) Christian  )

Assuming you mean an OBIEE analysis:

If you set an aggregation like MAX or MIN on your "Planned Code" you get just what you look for.

As you look for any value except a NULL ("the first not null" doesn't mean much in a database), all you need is to aggregate that column and problem solved.