5 Replies Latest reply on Feb 17, 2017 8:23 PM by Thomas Dodds

    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

        • 1. Re: Formula Error
          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?

          • 2. Re: Formula Error
            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?

            • 3. Re: Formula Error
              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?

              • 4. Re: Formula Error
                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)

                • 5. Re: Formula Error
                  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