6 Replies Latest reply: May 15, 2014 9:58 AM by Ontiveros _ RSS

    Help with a Calculated field

    Ontiveros _

      I need to get a rate out of a field, which is  called SECURITY (character field). What I am looking for is to get a certain parts out of the field that contains a decimal. Below are examples of what I have:

       

      0.600 FGPC N30158 G

      04.356 FGPS D

      FMND 05.020 DFKKIY

      8 1/8 BOND 24

       

      From the first two lines I want the 0.600 and 04.356, from the third line I want the 05.020.

      Since the fourth line does not have a decimal in it, I want it to be 0.0000. Does anybody have any idea how I would accomplish this?

        • Help with a Calculated field
          Grant Perkins

          There are a number of different approaches that could be built into an IF() statement (for example) that would deliver the results.

           

          However, are these formats the only possibilities or are there more ways and positions in which the decimalized value may (or may not) appear?

           

          If I work through an example of one way (maybe more than one way?) to extract the information you want will you feel OK about applying and extended that approach to other variants?

           

           

          Grant

          • Help with a Calculated field
            Ontiveros _

            Certainly.

            • Help with a Calculated field
              Ontiveros _

              What if placed as a calculation is the following:

               

              If(SECURITY .In.("."),SECURITY," ").

                 

              Theoretically, this should work. One reason I come up with as to why it won't is because the data comes from a .pdf import.

              • Help with a Calculated field
                Ontiveros _

                I actually used the IF statement after breaking the field up into 3 parts using " ". After doing this, I get blank results as if there is no decimal there.

                • Help with a Calculated field
                  Grant Perkins

                  This seems to work if the 3 part string to be split is called "Datafield". My field is defined as CHARACTER rather than numeric to preserve the leading zeros but could of course be converted to numeric easily enough for calcualtions if required.

                   

                   

                  If(Instr(".",Datafield)=0,"0.0000",

                  If(Instr(".",LSPLIT(Datafield,3," ",1))>0,LSPLIT(Datafield,3," ",1),

                  If(Instr(".",LSPLIT(Datafield,3," ",2))>0,LSPLIT(Datafield,3," ",2),

                  "Value")))

                   

                  Basically this checks for a "." somewhere in the string. If not found the value is set to 0.0000.

                   

                  The next IF LSPLITS the string and performs the same check on the first part of the string using a space separator.

                   

                  The one after that checks the second part of the split string (I kept the basic formula the same even whe not really required to for easier cut and paste when creating and also subsequent maintenance if required.)

                   

                  If the word "Value" appears it means the "." exists somewhere in the string but not in the first or second split components.

                   

                  I looks a bit unpleasant and I may have left some options for error on different data samples but hopefully it will get you close to a solution.

                   

                  There may well be a neater way to do do it and I fully expect someone to be along soon with a better answer but in the meantime this should work.

                   

                  HTH.

                   

                  Grant

                  • Help with a Calculated field
                    Ontiveros _

                    That seemed to do the trick, thank you so much.