7 Replies Latest reply: May 15, 2014 9:55 AM by Kevin Storey RSS

    Stripping out Decimal point in number

    Kevin Storey

      Can anyone tell me how to take a number like 235.48 and strop ou the decimal point? I also need to add one leading zero to the number. I would expect to change the number to a character to get the zero to stay. Can anyone help me?

       

      Thanks,

       

      Kevin

        • Stripping out Decimal point in number
          Kevin Storey

          Other than my obvious spelling mistake  smile.gif[/img] , I seemed to forget to mention that I am looking for the output to look like this:

          023548

           

          Thanks,

           

          Kevin

          • Stripping out Decimal point in number
            Nick Osdale-Popa

            As long as your number is 2 decimals, I came up with this solution:

             

            Str(int(Mynumber),4,0,"0")+LSplit(str(Mynumber,15,2),2,".",2)

             

            The 15 is the length of the actual number, I just used a high number to account for that many characters; adjust as necessary.

            • Stripping out Decimal point in number
              Grant Perkins

              Kevin,

               

              Are you simply converting the number to a character form or do you need to do this as part of an export? (Say to pass data to another system?). If it's for export there are some facilities provided if you are exporting to a text file. (See "Export and print options" for details. There's a useful section in the Help as well).

               

              If that fails to help, Nick's formula looks fine but I guess you could also get rid of the decimal by multiplying by 100 and taking the integer. Note that the leading zero fill Nick has used is a feature of Version 7. If using an earlier version an alternative approach is required to get you leading zero.

               

              So, if you don't have V7 but are always including a single leading zero the concatenation (Character field required) is simply to + the "0" with the new field. Something like

               

              "0"+str(NUMBER_field*100) ought to be close.

               

              On the other hand if there are a different numbers of leading zeros required depending upon the value in "Number_field" a different approach is required. IIRC I have seen a very elegant solution somewhere but can't quite remember where or how to find it at the moment.

               

              One way is to take the de-decimalized integer number, add it to 1,000,000 and convert the result to a character basis using STR. The use the RIGHT function for 6 chars to get rid of the "1" at the beginning! Something like:

               

              right(str((NUMBER_field*100)+1000000),6)

               

              The other way it to check the length of the de-decimalized string and work out how many zeros need to be added at the beginning rather than assuming it will always be one.

               

              V7 or the Export Options solution are certainly the easiest solutions to think through.

               

              I hope this helps.

               

              Grant

               

               

              Originally posted by Kevin Storey:

              Other than my obvious spelling mistake   smile.gif[/img]  , I seemed to forget to mention that I am looking for the output to look like this:

              023548

               

              Thanks,

               

              Kevin /b[/quote]

              • Stripping out Decimal point in number
                Nick Osdale-Popa

                Thanks, Grant.  I can always count on you for a more detailed explanation.      

                 

                You're right, I forgot that the Fill option is a v7 feature, so yes, a more elaborate approach is necessary.

                 

                Also, I believe the trick for adding leading zeroes was in one of the Monarch Newsletters.  Maybe one of the Datawatchers could post the formula here?

                 

                [size="1"][ December 03, 2003, 09:35 PM: Message edited by: Nick Osdale-Popa ][/size]

                • Stripping out Decimal point in number
                  Steve Caiels

                  Hi,

                  The formula for adding the correct number of "0"s to pad a character field is

                  [font="courier"]right("000000"+trim(orig),6)[/font][/quote]where the original character field is called orig and you want a 6 digit output.

                   

                  The conversion to strip decimals from a numeric field and pad with "0"s would be

                  [font="courier"]right("000000"+trim(str(orig*100,6,0)),6)[/font][/quote]Cheers

                  Steve

                  • Stripping out Decimal point in number
                    Grant Perkins

                    That's the one!

                     

                    Thanks Steve. I knew I would forget it. It's quite similar to the 'add to a million' (or suitably much larger number) method and somehow I always fixate on the numerical route. (Especially when posting at some ridiculous time of night.    )

                     

                    I will have to practice more ...

                     

                     

                    Grant

                     

                    [size="1"][ December 04, 2003, 04:20 AM: Message edited by: Grant Perkins ][/size]

                    • Stripping out Decimal point in number
                      Kevin Storey

                      Thanks everyone....I will try these immediately...

                       

                      Kevin