7 Replies Latest reply: May 15, 2014 9:55 AM by donh _ RSS

    Padding Zeros

    donh _

      Being a novice in monarch, is it possible to pad either character or numeric fields with leading zeros to make everything 4 digits?

       

      I am working with a report in monarch that brings in the acct number and description.  I successfully split the 2 apart and now have 2 character format columns (“acct number” and ”description”).  I have also successfully converted the character format “acct number” column into a numeric format column.

       

      I would like to pad either of the “acct number” columns with leading zeros to make them 4 digits then concatenate it back with the “description” column.

       

      (I have also encountered difficulty concatenating in monarch if one column is character format and another column is numeric format - - - but that is another story)

       

      I will be exporting the finished product into another system that will sort as windows explorer does - - - ie puts all of the 1s together, 2s together, . . . :

       

      1 abc

      11 def

      111 ghi

      1111 jkl

      2 mno

      22 pqr

      222 stu

      2222 vwx

       

      when I would like it to be like:

      0001 abc

      0002 mno

      0011 def

      0022 pqr

      0111 ghi

      0222 stu

      1111 jkl

      2222 vwx

       

      Does anyone have any ideas?

       

      Thanks

       

      Don

        • Padding Zeros
          donh _

          I was able to get it to work using IF, Len, and Stuff

          • Padding Zeros
            Grant Perkins

            Don,

             

            This parallel post has just concluded - basically the same as your problem.

             

            [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=001184"]http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=001184[/url]

             

            With the exception of the definitions and internal workings of certain types of database tools, a numeric field would not normally contain leading 0's since they are numerically rather pointless. Indeed a numeric Account number field is rather pointless since there would rarely if ever be any reason to apply any math to it.

             

            In reality an Account 'number' is a character field which just happens to contain only numbers. Often the reason for that is that the simple way to provide automatic and unique account reference identification is to use an incremental numeric sequence. If people then choose to start there records from number 1 rather than, say, 100000 in a six digit field they will find the consequences you are finding when a few records have been added.

             

            It's not wrong. It's not a problem. But it does not seem 'right' to the way we have been trained to 'see' things!

             

            The concatenation question is simple in that you cannot concatenate numeric fields because they are tfor summing. Similarly you cannot sum character fields, but can concatenate them.

             

            If you skipped your conversion of part of the original extract to become a numeric field you would not have had the problem of concatentation. With the original character data being, I assume, left justified (rather than right justified for a numeric field), I would guess the sort order would have been as required as well. (Although there could be some problems with that assumption under certain conditions.)

             

            On the other hand I assume you are now entirely expert on converting character fields to numeric and vice versa - which may well be a very valuable skill for future Monarch modelling.   :cool: 

             

            Even better, you have grappled with IF, LEN and STUFF and won, all of which suggests you have found, or are about to suddenly find, yourself much more comfortable looking at an input file and and seeing just how to get the results you need from it. And that is excellent as it puts you in control.

             

            All the best.

             

             

            Grant

            • Padding Zeros
              Nick Osdale-Popa

              Just to add to the conversation:  There are certain cases where a truly numeric number would need to have leading zeroes.  One such instance that I'm using is for our datafile for W2's.  The numbers are left padded with zeroes and decimals are dropped to fullfill a data field. Thus if a data field requires 15 placesmarks, and I'm transferring an amount that's $123.45, that number gets translated to 000000000012345. Thus you have a numeric padded field that will, eventually, have math applied to it.  I'm sure you can still argue that the number is character based, the zeroes are just placemarks and that the zeroes will be truncated when read into the recieving database.

               

              Just my 000000000000002 worth.

              • Padding Zeros
                Grant Perkins

                Hi Nick,

                 

                Yep, you are quite right. If a value is truly numeric and needs to be exported in the certain format, padded with leading zeros for example, that it is quite justified. It's one of the things I sort of implied when I mentioned 'exceptions', though to be fair if the need exists it is hardly an exception!

                 

                The "Options" settings for exporting to a text file offers a standard for this, which is mentioned in the referenced thread.

                 

                The thing is though I have rarely, if ever, come across a number-only based Account Number (or similar) data field where any math was to be applied, so the normal way of thinking about such fields seems to be best in character terms with the capability to convert to numeric  for specific purposes being held in reserve in case some form of math calculation is required  -perhaps as part of an audit process or maybe some sort of security exercise.

                 

                So it just seem easier, most of the time, to think of the field as a character field.

                 

                IMHO.

                 

                Grant

                • Padding Zeros
                  donh _

                  We have just purchased a 6 figure software system that allows us to push reports out web based as opposed to paper (an attempt to go paperless).

                   

                  With the way that the software works - - - with it's drilldown capabilities - - - it is important to keep the account and description together in one field.  The software reads as text for sorting (even though the original report generated does not) so to keep things sequential is why we need to pad.  In this instance where subaccounts can be from 1 - 9999 there has to be a logic formula involved too.

                   

                  I consider myself an excel guru - which does not help me here - but did allow me to come up with this formula (which may not be the best way but it did work).

                   

                  I just taught myself monarch last week and am being tossed into the fire as admin wants to see results - - - asap.  Finding this forum has REALLY HELPED.

                   

                  [font="courier"]IF(LEN(LSplit(Item,2," ",1))<2,Stuff(LSplit(Item,2," ",1),1,0,"000"),

                  IF(LEN(LSplit(Item,2," ",1))<3,Stuff(LSplit(Item,2," ",1),1,0,"00"),

                  IF(LEN(LSplit(Item,2," ",1))<4,Stuff(LSplit(Item,2," ",1),1,0,"0"),LSplit(Item,2," ",1))))][/font][/quote]Then follow that up with:

                   

                  [font="courier"][Item Number To 4 Digits]Space(1)[/font][/quote]

                  • Padding Zeros
                    Nigel Winton

                    Am I missing something, but as the end result appears to be a character field, would not the STR function have been useful in this case?

                     

                     

                    Nigel

                    • Padding Zeros
                      donh _

                      Thanks - - - I think we are seeing, as they say,  that there is more than 1 way to skin a fox

                       

                      I will try using the STR function too.

                       

                      Being a novice - I know what I need but just having problems getting there.

                       

                      In another report today I couldn't get the other formula to work so I used:

                       

                      [font="courier"]right("0000000000"+trim(orig feild name),10)[/font][/quote]I also now now that the "0" and last number need to be the same.

                       

                      Big thanks