6 Replies Latest reply: May 15, 2014 10:07 AM by karen123 _ RSS

    character vs numeric fields

    karen123 _

      i am still a novice with Monarch. Thanks for your patience.

      In the column of my report there are positive and negative numbers both preceeded by leading zeros.  If i change the field properties to number the negative amounts are shown as 'null'.  If i change the field properties to character any negative numbers do not convert properly into Excel. is there a function or formula i can use on the entire column to remove any leading zeros and have all read as numbers? Thanks

        • character vs numeric fields
          Nick Osdale-Popa

          Are you sure you have your trap defined correctly? I just created a simple text file:

           

          -000001

          -000123

          0003456

          0000002

           

          And was able to properly define it as a Numeric trap. Monarch converted the numbers correctly.

           

          Can you provide a sample - scrubbing any sensitive data?

           

          Thanks.

            • character vs numeric fields
              karen123 _

              0000240.00

              0000124.03

              0000150.00

              0000076.00

              0000116.00

              0000026.00

              000-150.00

              0000-76.00

              000-116.00

              0000-26.00

              0000000.00

              Here's a sample from the report. My minus sign is in the middle of the field.  Thanks.

                • character vs numeric fields
                  Nick Osdale-Popa

                  Ah, OK.

                   

                  This off the top of my head, so there may be a better way:

                  Trap it as Character. Then make a calculated field as numeric and use this formula:

                   

                  VAL(If(instr("-",field)=0,field,substr(field,instr("-",field),len(field))))[/b]

                   

                  Where field is the name of your character field.

                   

                  This searches for the minus sign within your field. It it doesn't find it, then just return the field back. If it does find it, it returns the substring of your field starting at the point where it found the minus sign. Either string is passed back to the VAL() function to convert it into a number.

                   

                  EDIT: After actually toying with it in Monarch, I've come up with this alternate formula:

                   

                  Val(substr(field,max(1,instr("-",field))))[/b]

                   

                  instr() will return the position of the minus sign if found or 0 if not. Since Substr() can't use 0 as a starting point we use MAX() with 1 so that if it's 0, it will return 1. You don't really need the last part of Substr() Monarch will just return the correct length of the field.