6 Replies Latest reply on Sep 23, 2015 9:28 PM by Gianni Ceresa

    split the data in obi

    Sree!!!

      H Experts,

       

      I have the data in the column like   for exapmple

      Productname - Brand - LOB - year

       

      Dellinspron - Dell - LOB1 - 2014

      data is coming in single column

      I want to divide this single column data in to the 4 columns.

      I have divide the data for first value

       

      SUBSTRING("TableName"."Columnname" from 1 for Locate('-',"TableName"."Columnname")-1)

       

      Then It will gives Dellinspron for the first column

      the same as I want

      Dell

      LOB1

      2014

       

      Can you please add your thoughts here.

       

      Thanks in Advance!!!

        • 1. Re: split the data in obi
          Christian Berg

          Gianni Ceresa will have something to do tonight

           

          Something like this

          1st bit you have

          2nd SUBSTRING('Dellinspron - Dell - LOB1 - 2014' FROM EVALUATE('INSTR(%1, '' - '', 0, 1)', 'Dellinspron - Dell - LOB1 - 2014'))

          3rd SUBSTRING('Dellinspron - Dell - LOB1 - 2014' FROM EVALUATE('INSTR(%1, '' - '', 0, 2)', 'Dellinspron - Dell - LOB1 - 2014'))

          4th SUBSTRING('Dellinspron - Dell - LOB1 - 2014' FROM EVALUATE('INSTR(%1, '' - '', 0, 3)', 'Dellinspron - Dell - LOB1 - 2014'))

           

          It's not super optimized nor very nicely written but it gives you an idea

          • 2. Re: split the data in obi
            Sree!!!

            Hi Gianni,

             

            It is not working!!

            • 3. Re: split the data in obi
              Felipe_Idalgo

              Hi,

               

              What about result?

               

              Tip: Ensure EVALUATE (If I remember EVALUATE_SUPPORT_LEVEL) is enable inside NQSCONFIG.INI

               

              Felipe Idalgo

              • 4. Re: split the data in obi
                Sree!!!

                Felipe,

                 

                I am trying above formula in the edit formula of the report it is throwing error

                 

                SUBSTRING("TableName"."Columnname" from 1 for Locate('-',"TableName"."Columnname")-1)

                 

                I divide the first value from the column I need 2, 3, 4 values

                 

                Thanks!!

                • 5. Re: split the data in obi
                  Sree!!!

                  Any comments above query!!!

                  • 6. Re: split the data in obi
                    Gianni Ceresa
                    It is not working!!

                    Sorry, we all forgot our crystal ball at home ...

                     

                    Why? Do you have an error message? Do you have an output but it's not what you expect?

                     

                    If you don't have any error and you just have something that's not what you expect I would like to highlight part of what Christian posted: not super optimized nor very nicely written but it gives you an idea

                     

                    So read it, understand it, find the correct version for your need and use it.

                     

                    If you want to stay purely in OBIEE without EVALUATE (the SQL will really looks bad, be ready for that) you only need to extend your first formula to give you the first element for the others.

                    You have this for the first one : SUBSTRING("TableName"."Columnname" from 1 for Locate('-',"TableName"."Columnname")-1)

                    So you agree that the second value start at Locate('-',"TableName"."Columnname"), so now all you need to do is write a formula to find where does the 3rd '-' is and just write the new formula etc.