Extract the ] delimiter value using Evaluate function in OBIEE RPD and Analytics

Question

Hi All,

I Have below field called amount . Requirement is to extract each amount value in seperate columns like amount1,amount2,amount3…..etc

2686.319]2771.295]2601.976]2805.035]2721.763]2507.761

663.666]663.666]663.666]663.666]663.666]663.666

785.400]785.400]785.400]785.400]785.400]785.400

1569.180]1569.180]1569.180]1569.180]1569.180]1519.180

963.290]963.290]963.290]963.290]963.290]963.290

374.490]374.490]374.490]374.490]374.490]374.490

316.601]307.600

493.387]495.000]495.000]495.000]489.500]495.000

1361.170]1373.100]1351.390]1343.040]1376.440]1383.120

623.220]623.220

Need to extract the each value by using evaluate function in OBIEE RPD and analytics. Tried substr and instr and its not working.

Please provide inputs/suggestions on the same.

Thanks & Regards,

Gianni Ceresa · Answer

Ok, first thing: you can get rid of EVALUATE, you don't need it. The strange thing is that you have an hardcoded value for the position where to start the SUBSTRING for Month2, does this mean that you know the exact position of all the ']'? In that case remove EVALUATE and just enter the numbers directly. There is a LSQL function doing the same job as the SQL INSTR function: Locate(, , ) This one will let you make a full LSQL dynamic formula (but as said: if your sizes are fixed, then enter those numbers instead of calling functions for no reason). Because LOCATE take as 3rd parameter the position from where to start looking, you will need to call it multiple time as parameter for LOCATE itself to move from the first ] to the second ], to the third ] etc. Again: if you do it with SQL, it's a lot easier, but real SQL, not just pieces of SQL added in many places with EVALUATE. I would highly suggest you consider an opaque view in your RPD (the "Table type" = "Select") or as a real database object.

SudheerK · Answer

I have tried below in OBIEE RPD and ANalytics to get the Month1 and Month2 amount values

--SUBSTRING("Core"."AMT" FROM 0 FOR cast(EVALUATE('instr(%1,%2)' ,  "Core"."AMT",']')as int)-1)--Month1

--SUBSTRING("Core"."AMT" FROM 9 FOR cast(EVALUATE('instr(%1,%2,%3)',"Core"."AMT",']',2)as int))--Month2

its working for to get the 1st and 2nd values but whereas while trying same logic for other values…its not giving results as expected.

any further inputs extend to this.

Gianni Ceresa · Answer

Hi,

Tried substr and instr and its not working.

What did you try exactly? Any particular reason for wanting to use EVALUATE?

In simple SQL, with SUBSTR and INSTR you can do the job, adding some extra logic around it to manage boundaries.

Also, if you have a fixed, constant number of "]" per row, it will be a lot simpler. If the number of "]" is dynamic and change row by row, it will take some extra conditional logic.

All in all, using EVALUATE for this will be really poor to maintain. You could consider an opaque view in the RPD instead, it will at least keep the whole SQL query in a single place. And from a performance point of view, doing it in your ETL or database directly (maybe with a materialized view), will be the best for performance of your queries.