5 Replies Latest reply: May 15, 2014 10:10 AM by mdyoung _ RSS

    Help with formula to zero fill

    a2z143 _

      I have this report where I am picking up amounts but also have to zero fill where there is no data in that column. Formula help needed. Thanks!

       

      000014086

                  

                  

      000019540

      000052107

      000012624

                  

                  

                  

      000006617

      000004734

      000018750

      000017646

      000030000

        • Help with formula to zero fill
          Olly Bond

          Hello a2z, and welcome,

           

          Assuming this is a text report, then you can fill in the blanks in two ways:

           

          In the Field Properties, you can define blank rows to be completed with the value of the previous record.

           

          If you want to replace null values with 0, the easiest way is to define, in the Table window, a calculated field:

           

          = if (isnull()=1;0;[Field1])

           

          Hope this helps,

           

          Olly

            • Help with formula to zero fill
              a2z143 _

              Thanks for your quick response Olly. I am recieving an "Operand types" error.

               

              My heading for the column is CS1 and my new calculated filed name is CS1A.

               

              = if (isnull()=1;0;[CS1])

                • Help with formula to zero fill
                  Olly Bond

                  Hello a2z,

                   

                  I was assuming that the fields were numeric, but in less haste I can see the leading zeroes.

                   

                  Does if(isnull()=1;"000000000";[CSA1]) solve the issue?

                   

                  Best wishes,

                   

                  Olly

                    • Help with formula to zero fill
                      a2z143 _

                      got it... thanks Olly...

                        • Help with formula to zero fill
                          mdyoung _

                          I'm all the time having to produce fixed length files for interfacing between systems which require a certain lenth string filled with either spaces or zeros and left or right justified.... blah....blah....blah. So, I created a FILL user-defined function and I'd like to share it in hopes that it will help you out; especially, if you do it quite often.

                           

                          Create a new user-defined function and define as follows:

                          1. Name: Fill

                          2. Description:

                          Fill(s,n,c,j) - Returns a string the <2>length of n</2> that contains the <1>string s</1> and filling the remaining character length with the <3>character c</3> with a <4>justification of j (R or L)</4>.[/CODE]
                          NOTE: The <> and </> tags in the description are used to tell Monarch which words to bold when defining your parameters in the calculated field's formula section.

                           

                           

                          3. In the Forms section, click the add button.

                          4. On the parameters tab, add the following parameters:

                          Name    Type            Test Value

                          s       Character       4567

                          n       Numeric         8

                          c       Character       0

                          j       Character       R

                          /CODE

                          5. Select Character as the return type

                          6. On the Formula tab, add the following code

                          Stuff(/SIZE

                             Replace(Space(n)," ",c)[/SIZE]

                          ,  If(j = "R", n - Len(s) + 1, 1)[/SIZE]

                          ,  Len(s)[/SIZE]

                          ,  s[/SIZE]

                          )[/SIZE]

                           

                          /CODE

                          7. To test, go back to the Parameters tab and click Test. You should get the result 00004567

                          8. Click OK.

                          9. In the Categories section, select String

                          10. Click OK and you're done.

                           

                           

                          Usage:[/B]

                          The 1st parameter (s) is the passing field value.

                          The 2nd parameter (n) is the length of the returning string

                          The 3rd parameter (c) is the character you wish to fill remaining spaces with

                          The 4th parameter (j) is the justification of the returning string (L or R).

                           

                           

                          Examples of usage:[/B]

                           

                          For right justified, zero filled that's 10 characters in length:

                          Fill(Field, 10, "0", "R")[/SIZE]

                          [/SIZE]

                          If the Field value is "123", the returning result will be "0000000123".

                          /SIZE[/CODE]

                           

                           

                          For left justified, space filled that's 20 characters in length:

                          Fill(Field, 20, " ", "L")[/SIZE]

                          [/SIZE]

                          If the Field value is "hello", the returning result will be "hello               ".[/SIZE]

                          /CODE

                           

                           

                           

                          Hope this helps.

                           

                          Micheal