7 Replies Latest reply: May 15, 2014 9:54 AM by Data Kruncher RSS

    missing a zero

    Shep _

      I am pulling dollar amounts from a report.

      They are Type=CHAR

      The report contains only dollars - no cents.

      They display in Table as: $91,228 or $760


      In the end I need the numbers only and to add '00' as place holder

      for cents (no decimal) for exportupload files.


      So, I created a calculated field with formula:


      to strip the dollar sign'$' and comma seperator','

      when present then adding trailing two zeros'00' as a place holder.



      $91,228 will display as 9122800 as expected

      But, $760 will display as 7600 (missing an expected zero)



      Am I missing something, using wrong function, taking wrong approach?

        • missing a zero
          Grant Perkins



          I don't get the same result as you. That mnay not help much ...


          Firstly if I set up your value samples as lines in a notepad file formatted as





          and then read the file using Monarch, trap on the $ , define the field and view the table I get the numbers without any formatting just as you want them EXCEPT for the 00 placeholder. It's a numeric field so multiply by 100 and you will have the result you want. Then convert to character if you need to.


          If I make the incoming field character (as you may need to have as part of your extraction of course) and apply you formula it works fine BUT the disply is not consistent if it was originally right justified.



          [font="courier"]$ 91,228

          $    760[/font][/quote]becomes something like

          [font="courier"] 9122800

              76000[/font][/quote]Which makes me wonder if you are actually getting the correct result but have a display setting anomaly which is hiding the second concatenated zero from the 76000 value. Is the calculated field set to left or right justified display? Is the field wide enough for both data and display?


          I could be missing things by a long way but this looks like it should be simple as you say so for me it's worth checking the simple things with you first.







          [size="1"][ July 19, 2007, 05:24 PM: Message edited by: Grant Perkins ][/size]

          • missing a zero
            Shep _


            Initial review considering your feedback:

            Original and Calculated fields are:

            Display Width=11

            Alignment = Right

            Template Width=11

            *I applied 'Autosize Clmn Width', no help.

            I tried changing alignment but same result.

            I have also noticed when I type $760 as a TEST within the calulated field formula. . I get the expected result.

            But, seemingly does not carry through.


            Weee, my first attempt using the CODE function here to show what I'm seeing.


            [font="courier"]An example of my table display:

            $1,301,027       130102700      $1,301,027       130102700

               $361,550        36155000        $361,550        36155000

                $50,963         5096300         $50,963         5096300

                   $760            7600            $760            7600

                   $340            3400            $340            3400

               $165,939        16593900        $165,939        16593900



            An example of the report display:

            $1,301,027       $1,301,027

               $361,550         $361,550

                $50,963          $50,963

                   $760             $760

                   $340             $340

               $165,939         $165,939  /font[/quote]

            • missing a zero
              Bruce _



              In your calculation you are looking for a "$," which is not present for 760. (it only has $)


                   So, I created a calculated field with formula:

              Strip(,"$,")+"00" /quoteGrant's idea is the best solution, pick up the numbers as numbers, multiply by 100 and export (or change to text first). That way you do not need to worry about $ or comma(s) in the number.


              Let us know how you make out.   smile.gif[/img]

              • missing a zero
                Grant Perkins



                If I type you report display (looks right justified) into a txt file and then display that in Monarch I get







                   $165,939                                 /font[/quote]Which is interesting.


                I still think it is something to do with the display. What happens if you set the DISPLAY AND DATA width for the field to, say, 20 ?


                Also try  making the Alignement LEFT rather than right.


                Can you see everything then?


                We can deal with the display format once we have found the missing characters!




                • missing a zero
                  Shep _


                  I will go back and check that, but, at the moment I've I finally got around to going with NUMERIC *100 with good result.


                  GrantBruceany nice Monarch folk:

                  My next goal is to have Leading Zeros up to 11 positions.  76000 as 00000076000


                  Can I do that within the same Monarch calc-field formula?

                  • missing a zero
                    Bruce _



                    Check out the help for the STR function. There is an option to use a padding character when coverting numbers to a string. Use the results from Grant's method to supply input for the STR function.


                    Have fun.



                    • missing a zero
                      Data Kruncher

                      Hi Shep,


                      Have a look at  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000361;p=1#000005"]Steve's padding solution[/url] from awhile ago.