OTBI formula to modify string

Question

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"

Federico Venturin - Oracle · Accepted 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 · Accepted 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.