Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

OTBI formula to modify string

Accepted answer
197
Views
3
Comments
Raluca Balan-Oracle
Raluca Balan-Oracle Rank 2 - Community Beginner
edited Jan 11, 2024 10:45AM in Oracle Transactional Business Intelligence

Hello,

We have an analysis in OTBI from one subject area with strange behavior - in the column 'Employee' the values are in the format 'LastName, FirstName' (Doe,John), but when I use same column for filter, the values appear in format 'FirstName LastName'(John Doe)

I am trying to use column functions to modify from the string from 'Doe,John' to 'John Doe' to match the filter values.

But I am receiving errors when trying to use instr function, because there are limited functions that can be used in OTBI Analysis ( the SQL function works in BIP, but we need in BI Analytics).

Can you please help on this one? I haven't work with OTBI in quite a long time...much appreciated!

------------------------------------------------------------------------------------------------------------------------------------------------------------------

Formula syntax is invalid.

[nQSError: 10058] A general error has occurred. (HY000)

[nQSError: 43113] Message returned from OBIS. (HY000)

[nQSError: 27042] Function INSTR is not defined by administrator. (HY000)

SQL Issued: SELECT INSTR ("Employee"."Employee Name", ',', -1) FROM "ExpTransactions Real Time"

Best Answers

  • Federico Venturin - Oracle
    Federico Venturin - Oracle Mod
    edited Jan 11, 2024 9:30AM Answer ✓

    Hi @Raluca Balan-Oracle ,

    Are you sure that you are using exactly the same expression for the filter? It could be that in your analysis the column has a different/custom expression, but the same name/header as another column.

    However, I would transform 'Doe,John' to 'John Doe' as follows (assuming that there is only 1 comma character in your strings):

    RIGHT("Employee"."Employee Name", LENGTH("Employee"."Employee Name") - LOCATE(',', "Employee"."Employee Name")) || ' ' || LEFT("Employee"."Employee Name", LOCATE(',', "Employee"."Employee Name") - 1)

  • Raluca Balan-Oracle
    Raluca Balan-Oracle Rank 2 - Community Beginner
    Answer ✓

    Hi Federico,

    Thank you so much!! it worked!!

    I'm using same expression and functions in BIP - SQL datamodel and there I don't receive any errors.

Answers