5 Replies Latest reply: May 15, 2014 10:14 AM by Grant Perkins RSS

    How to account for < and > brackets as negative sign for numbers

    FMarion _

      I have data with < and > brackets that I can only show as text/character when I really want Monarch to read as negative numbers.  How do I get Monarch to recognize these brackets so it will show up as a negative number?

        • How to account for &lt; and &gt; brackets as negative sign for numbers
          Olly Bond

          Hello FMarion,

           

          The easiest fix is probably to define a calculated field in the table as:

           

          if(left(TextField;1)="<";-1*val(TextField);val(TextField))

           

          Best wishes,

           

          Olly

            • How to account for &lt; and &gt; brackets as negative sign for numbers
              Grant Perkins

              If the source is a report of text file and the < and > symbols are not used elsewhere in the report you could use features in the Monarch Utility (or indeed just an editor program) to change the characters before processing in Monarch. It should be possible to run this operation using a batch process file if it is something you do regularly.

               

              As an alternative to Olly's suggestion ...

               

              Still using the calculated field idea you could use the 'Replace' function to change the < to ( and > to ) or simple get rid of one of them and replace the other with a minus sign then VAL() the result. However the only possible benefit this may offer, compared to Olly's suggestion, is that it might just be slightly quicker to work out what is going on in the formula when you (or someone else) re-visits the model in 2 or 3 years from now ....  

               

              I don't recall previoously seeing a system that used < > brackets for negative numbers. For future reference could you tell us the source of the output you are analysing? Preferably the software system that that is putting out the data in that form but you may not be able to discover that.

               

              Thanks.

               

               

               

              Grant

                • How to account for &lt; and &gt; brackets as negative sign for numbers
                  elginreigner _

                  You could also do a replace() on a character field to '(',')' then convert to a numeric.

                    • How to account for &lt; and &gt; brackets as negative sign for numbers
                      Nigel Winton

                      Grant

                      For info Epicors Avante will use <> for negatives in some reports. It seems to be a user preference thing.

                      I have enjoyed this problem for 14 years and yours and Olly's suggestions are the easiest way around the problem. One small difference I use the Right Hand > to check for the negative as with a right justified character field that was a fixed size and the data varying size the Left number 1 character could be a space, whereas the right would always be a number or >.

                      So my formula would be IF(RIGHT(OPBAL,1)=">",-VAL(OPBAL),VAL(OPBAL)) where OPBAL is the character field.

                        • How to account for &lt; and &gt; brackets as negative sign for numbers
                          Grant Perkins

                          Grant

                          For info Epicors Avante will use <> for negatives in some reports. It seems to be a user preference thing.

                          I have enjoyed this problem for 14 years and yours and Olly's suggestions are the easiest way around the problem. One small difference I use the Right Hand > to check for the negative as with a right justified character field that was a fixed size and the data varying size the Left number 1 character could be a space, whereas the right would always be a number or >.

                          So my formula would be IF(RIGHT(OPBAL,1)=">",-VAL(OPBAL),VAL(OPBAL)) where OPBAL is the character field.[/QUOTE]

                           

                          Nigel, thanks for that.

                           

                          I have to admit that I don't think have never heard of the system you mention but then I have no reason to have knowledge of all of them!

                           

                          Now, thanks to your information, we have the beginnings of a reference database!

                           

                          I suppose if there are enough systems that might make use of alternatives to the commonly used methods of the past there may be a case for loging a future idea for a enhancement?

                           

                          Grant