Categories
- All Categories
- 143 Oracle Analytics News
- 27 Oracle Analytics Videos
- 14.7K Oracle Analytics Forums
- 5.7K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 54 Oracle Analytics Trainings
- 12 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 2 Oracle Analytics Industry
- Find Partners
- For Partners
Extract first sets of integer from entries

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
Comments
-
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
0 -
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
0 -
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.
0 -
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 --> 4Best.
0 -
Wait so that is acutally an implicit aggregation / binning you're looking for?
Because that logic would at max give you 99 rows.
0 -
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
0 -
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
0 -
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.
0