5 Replies Latest reply: May 15, 2014 9:54 AM by Grant Perkins RSS

    Negative numbers

    Peter _

      Hi,

       

      I have a file that has the negative numbers as a character. Ex. L represents a 0. So -143 would be 14L. How can I convert it so that it displays as -143 on my table.

       

      Thanks

      Peter

        • Negative numbers
          Tom Whiteside

          Peter, I'm not completely clear on your syntax.  If you meant to say that the letter L represents a trailing minus sign, then Steve Caiels solved a similar problem last year, where brackets were used for negation (<number>).  His updated solution to this one would be a calculated field for:

           

          new=if(right(orig,1)="L",-val(trunc),val(orig))

           

          Be sure that "orig" is your original data that must be set as a character field, and that "trunc" is the "orig" with the "L" truncated.  If this is not the right interpretation, then I don't understand your posting.

           

          If this does work, then hoist a glass to Steve - - it's just about Happy Hour for him across the pond.        Cheers, Steve!

           

          Best wishes,

          • Negative numbers
            Peter _

            What is happening is that the zone portion of

            the least significant digit is holding the sign. So -143 becomes 143 by moving negative values into character fields for presentation. Another exaample is -5980 becomes 598}.

             

            Peter

            • Negative numbers
              Grant Perkins

              Peter,

               

              I used to be involved with a system written in a language where a -ve number was represented by a lower case alpha character. From memory it started at p which I think was 0- (zero as the last digit of the number string).

               

              Sounds like pretty much the same thing.

               

              I can't remember ever using Monarch to access a raw data field dump directly but we certainly had examples of conversion running for cross system interface field use.

               

              I haven't had a chance to try playing around with this but I guess what you need to do is create a calculated field that interprets the last character of the incoming text field, decides if it is pos or neg (is it numeric or alpha for example). If is is alpha then lookup the correct last digit value and make the number negative by a math calculation. (0 - number) and set the sign handling appropriately for the new calculated field.

               

              Basically that would seem to be a slight variation on the formula supplied by Tom/(Steve).

               

              Something like;

               

              new=if(right(orig,1)="L",-val(trunc*10)+ (translated numeric value of L),val(orig))

               

              (EDIT: See the later post for a more correct version of this formula! Depending upon where you put the parentheses you will either need to ADD the interpreted value and then make the resulting number negative OR make the first part negative and then SUBTRACT the interpreted number!)

               

              The "L" (or "}" in your example) will likely be a consistently used ASCii character number rather than a font based character. Checking for the ASCii char value of the last char in the field and interpreting that as a number is probably the best option.

               

              There may other approaches. Which version of Monarch do you have available? The answer shoould help to work out which approach might be best for you.

               

              Grant

               

               

                Originally posted by Peter:

              What is happening is that the zone portion of

              the least significant digit is holding the sign. So -143 becomes 143 by moving negative values into character fields for presentation. Another exaample is -5980 becomes 598}.

               

              Peter /b[/quote]

               

              [size="1"][ October 19, 2003, 11:52 AM: Message edited by: Grant Perkins ][/size]

              • Negative numbers
                Peter _

                Grant,

                I am currently using Pro V7.

                 

                Peter

                • Negative numbers
                  Grant Perkins

                  Peter,

                   

                  A few ideas for interpreting numeric character strings which have the final character as alpha to denote a net negative numeric value and the value of the least significant digit.

                   

                  Method 1  -  Possible in version 7[/b]

                   

                  Uses a lookup table approach.

                   

                  Identify the  ASCii value of the last character in the field called "In Val as Text".

                   

                  Asc(Right(,1))[/b]

                   

                  This will give a number. For example p = 112, q=113, r=114, s=115, t=116, }=125 and so on.

                   

                  Create a new calculated field. Check the ASCii value against a lookup table and convert the last character and field sign value if necessary.

                   

                  Sample of lookup table might be;

                   

                  p=-0       (ASCii 112)

                  q=-1     (ASCii 113)

                  r=-2     (ASCii 114)

                  s=-3     (ASCii 115)

                  t=-4     (ASCii 116)

                  }=-[whatever number is appropriate]

                   

                  So a section of our table might look like this;

                   

                  ASCii Val          Numeric Value to apply

                   

                  112                   0

                  113                  1

                  114                  2

                  115                  3

                  116                  4

                   

                  And produce a field called "Neg Numeric Value" which we can use in an IF function in the following formula to create another calculated field;

                   

                   

                  if(>=112,(-(Val()*10)-val()),val())[/b]

                   

                  To show this another way

                   

                  if(>=112,[/b]

                   

                  (I have set my range of negative numbers to sart at "lowercase p" so I can ignore anything below that but there are several different ways to create a working formula for this requirement).

                   

                  (-(Val()*10)-val()),[/b]

                   

                  (Val() will return the numeric part of the string with the last digit missing in the case of an aplha character, so we neet to multiply by 10. The "-" at the start will set the Val result to be negative and we also need to deduct the looked up value from our lookup table)

                   

                  val())[/b]

                   

                  (If the last character is not above ASCii 112 (in my example), it must be a positive numeric once we have converted it from text to a numeric field using VAL)

                   

                  There are several ways to approach this sort of assessment of a value.  For example the lookup table needs only the values that need to be interpreted. And then all we ned to do is make the lookup if the last character in the field is alpha rather than numeric. For example;

                   

                  if(IsAlpha(right(,1)),(-(Val()*10)-val()),val())[/b]

                   

                  will work where we know that the final value will be an alpha character.

                   

                  HOWEVER, if the final character is not an ALPHA character but is, say, a punctuation character such as "}", then the formula will NOT give the desired result. Hence the better option of using ASCii character values for the check.

                   

                   

                  Method 2 - Possible in V7 using the new STUFF function.[/b]

                   

                  Assuming we have a limited number of possible interpretations (in this case there are 10 possible numbers - 0 through 9) we could forget the lookup table and use a nested IF function formula as below to create a new numeric calculated field. (Example only)

                   

                   

                  if(right(,1)="p",-val(Stuff(,Len(),1,"0")),

                  if(right(,1)="q",-val(Stuff(,Len(),1,"1")),

                  if(right(,1)="r",-val(Stuff(,Len(),1,"2")),

                  if(right(,1)="s",-val(Stuff(,Len(),1,"3")),

                  if(right(,1)="t",-val(Stuff(,Len(),1,"4")),

                  if(right(,1)="}",-val(Stuff(,Len(),1,"0")),

                  val()))))))[/b]

                   

                  These always look somewhat complex but are quite easy to create where you have a repeating formula like this example.

                   

                  Here we start by checking to see if the last character in the string is "p". If it is we use LEN to get the length of the string which gives us the position of the last character. We than use that as the value for the character position for the start of our STUFF function, to change 1 character to the value 0 (zero).

                   

                  If it is not a "p" we check to see if it is a "q". If it is, we STUFF a 1 into the last character position.

                  If it is not a "q" we check to see if it is a "r". If it is, we STUFF a 2 into the last character position.

                   

                  And so on.

                   

                  If none of the checks give us anything to STUFF we assume it is numeric and use that value.

                   

                  It may have been better to make the first check the one that decided whather we are dealing with a numeric value anyway and then only do the IF checking if that indiactes that the result is in fact NOT numeric. However if our file is well populated with negative values the processing time difference will likely be very small.

                   

                   

                  Method 3 - Does not make use of V 7 features.[/b]

                   

                  A variation on the nested IF function theme. Again the formula checks the last character in the field and if it decides we have a match set the negative value and performs the calculation directly rather than via a lookup table.

                   

                  if(right(,1)="p",-(val()*10)-0,

                   

                  if(right(,1)="q",-(val()*10)-1,

                   

                  if(right(,1)="r",-val()*10-2,

                   

                  Val())))[/b]

                   

                  In the case of this fixed 'conversion' of 10 limited and constant values, using the formula above (or something similar) is probably more than adequate for most purposes.

                   

                  A lookup table might be more appropriate if the idea was to be re-used across several models or for reports from several sources which used different values for the last character. In which case the replication of the formula might be easier with no changes EXCEPT to the name of the lookup table or just the lookup table values.

                   

                  I hope this helps. I'm sure the formulae shown here can be refined somewhat and I look forward to suggestions of more elegant solutions.

                   

                  All the best,

                   

                  Grant

                   

                   

                  Originally posted by Peter:

                  Grant,

                  I am currently using Pro V7.

                   

                  Peter /b[/quote]