Extract first sets of integer from entries

Jonathan Chan gmg

Summary

How to extract the first sets of integer from entries in "New Calculated Measure"

Content

Hello Oracle Community,

In "New Calculated Measure" in Analysis, how can a new calculated measure be created to only keep the first set of integers after the letter "D" from the following example:

BEFORE:
ITEM NUMBER
---------------------
ABCD11HIJ150150  
ABCD12EFG152151
ABCD3EFG7230101
ABCD4HIJ2040100
.
.
.

AFTER:
ITEM NUMBER
---------------------
11
12
3
4
.
.
.

The following error occurs when I use "regexp_substr()" function and I do not have administrator access:
[nQSError: 27042] Function regexp_substr is not defined by administrator. (HY000)

Thank you.

Version

Oracle Business Intelligence 11.1.1.9.0

FPonte

Hi Jonathan.

Try Evaluate.

Something like this but you will need to work out how to build it to fit your purpose.

EVALUATE('REGEXP_REPLACE(%1,''^[A-Z]+'',''*'')' as char ,<you Dimension Level here>)

Gerardnico still useful  on this case as you are on 11g.

https://gerardnico.com/dat/obiee/obis/logical_sql/evaluate

Regards.

Fernando

Jonathan Chan gmg

Hello Fernando,

Thanks for reaching out.

Unfortunately my account is not enabled with EVALUATE support per below error message.  Thereby would you have alternative suggestions that do not involve EVALUATE functions?

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

Best

[Deleted User]

If you can't use EVALUATE - is the position of the numeric part fixed? Or can that vary?

Because without evaluate and fixed position it'll be an atrocious formula.

Jonathan Chan gmg

Hello Christian,

Thanks for reaching out again.

The beginning position is always the 5th starting from the left, at the same time the needed numbers can be single or double digits.  For example:

ABCD11HIJ150150  --> 11
ABCD12EFG152151 --> 12
ABCD3EFG7230101 --> 3
ABCD4HIJ2040100 --> 4

Best.

 

 

[Deleted User]

Wait so that is acutally an implicit aggregation / binning you're looking for?

Because that logic would at max give you 99 rows.

Jonathan Chan gmg

What is implicit aggregation / binning?

Max 99 rows would be fine because there are less than 50 records. 

The purpose behind this extraction is to single out the unit-per-case-pack-quantity info. within an item no.  Thereby the info. can be used to multiply with case pack quantity to determine the total number of units per item ordered from vendors.

For example ABCD12EFG152151 means this item comes in case pack of 12 units, and "12" is the target info. needed to be extracted.

Thank you.

Jon

FPonte

Hi Jonathan.

Any chances to ask for that permission (enable EVALUATE)?

Here another example with Regular Expression.

https://gerardnico.com/dat/obiee/obis/regexp_evaluate

Or another way would be to sort this in the database (or in the repository) creating a new attribute there.Not sure if possible as I don't know how are you consuming the data (BI Apps or Standard Star Schema).

Cheers

Fernando

Jonathan Chan gmg

1) Permission route is off for now.

2) Don't have access to modify the database nor repository.

3) Attached screenshot shows the interface that I am getting data from.  How may I find out "how" I am consuming the data?
 

Thank you.