Formula Error

Rigo

I have a formula in my column that only extracts numbers between dashes. The problem is when i take all my data filters off OBIEE, it does not provide me results. I need to tell the formula that is there is no text between the dashes then put a zero. I feel this would provide me results, but at the current moment I have hit a barrier. My formula is below. Any suggestions. Im very green to OBIEE.

FORMULA: Case when length(SUBSTRING(SUBSTRING("Service Request Attributes"."Problem Code" from Locate('-',"Service Request Attributes"."Problem Code")+2 for LENGTH ("Service Request Attributes"."Problem Code"))FROM 1 FOR -4+ LOCATE('-', SUBSTRING("Service Request Attributes"."Problem Code" from Locate('-',"Service Request Attributes"."Problem Code")+2 for LENGTH ("Service Request Attributes"."Problem Code")))))=0 then 0 end

Robert Angel

I am not sure exactly what you are trying to achieve, but have you tried adding an IfNull around the value that is text numeric, to default it to a format that your formula will presumably translate to zero?

Robert Angel

Btw, if I am understanding you correctly and you are just after getting a number from between marks, like this

'-33434.23-'

Then why not just use replace; -

replace('-33434.23-','-','')

Which in your case is; - replace(IfNull("Service Request Attributes"."Problem Code",0),'-','')

Or is your requirement more complex than that?

Rigo

Sorry I am completely Green  to OBIEE so thanks for your patients in advance.  Ok so what I am really doing is going into a problem category column that has text and numbers for example:

Problem Category

"Fix/Tubing/Conection - 369 - Tubing/Fitting".

"Conecct - 659687 - Fit"

Problem code Column the result is

row 1 "369"

row 2 659687

row 3 etc

So if I am understanding you correctly  if I just use the formula you stated - replace(IfNull("Service Request Attributes"."Problem Code",0),'-','') it should work rather than have the whole complex formula I put earlier?

Robert Angel

No, sorry as your format is more complex, but you do need to add the IfNull(......,0) to your original; -

IfNull("Service Request Attributes"."Problem Code",0)

Thomas Dodds

Parse out these items in the physical column mappings into their own logical columns ...

benefits:

1. let the database (strongest worker) do this work and not the OBIS or OBIPS machines
2. build once - reuse many