4 Replies Latest reply: May 15, 2014 10:02 AM by joe.lovati _ RSS

    Corey's "Column confusion"

    Data Kruncher

      [URL="http://www.monarchforums.com/showthread.php?goto=newpost&t=2623"]Corey (garncor) originally posted[/URL] in the KnowledgeBase forum:

                                                       I am attempting to extract data from a report using Monarch 9 Pro. There are two aspects to this report that are giving me problems and I was hoping someone out there could give me a hand.


      1.     When a value in one of the columns is negative, the minus sign appears after the value in question

      2.     I don’t think the company that delivers this report to me ever foresaw any payment amounts above $100,000


      When a payment amount above $100,000 appears on the report, the result is one column that sometimes contains a minus sign, which applies to the number to the left, and sometimes contains a digit, which is the first digit of the number to the right.


      I’m trapping the different values to the left and right in different templates but when I make the “right-facing” trap big enough to include the minus sign (which applies to the column to the left of the one I’m working on), sometimes I end up with a number with minus signs on both sides, for example “-5431.51-” Unfortunately, Monarch evaluates a number sandwiched between two minus signs as “0.00.” Is there a way that I can have Monarch disregard this leading minus sign and only consider the characters following it?






      Reposted here to keep it in the regular discussion area for future reference.

        • Corey's "Column confusion"
          Data Kruncher

          So a typical example might look something like this?

          5431.51 5431.51-5431.5115431.51 /codeWhere the first number is 5431.51, the second should be -5431.51, the third should be 5431.51, and the last is 15431.51?


          If[/B] this never floats along the line (your decimals are always in the same positions), and /Bthe positions immediately to the left and right of the value can contain only a space, a minus sign, or a number 1 to 9, then here's my suggested solution.


          Let's refer to those variable space/minus sign/number as unknown fields X, where there's a single character X field between each number, simply named X1 through X5, in these positions:


          X       X       X       X       X[/code]Then to determine the actual value of each field, we need calculated fields each with a formula similar to this formula to derive the first value:


          -1*if(x1 .notin. (" ","-"),


          if(x1 .notin. (" ","-"),




          If the X value to the left of the value (X1) isn't "-" or " ", then add that relative value to the original value, and if the x value to the right of the the original value (X2) is "-" then negate the original value.


          The slightly complicated formula should account for the (unlikely?) situation wherein you'd have a number in the left X position and a "-" in the right X position.


          Duplicate the calculated field, changing the field references as necessary, and if my assumptions as described above are correct, then that ought to do it for you.


          Clear as mud?

          • Corey's "Column confusion"
            joe.lovati _

            I came across a similar issue and resolved it by first trapping the two fields and naming them with a PRE and then used an if statement to correct the data.  In my sample below, there is either a 1 or a - occuring in the same vertical column, the 1 in the millions position would be trapped in the wrong field.  But I extracted these two fields as characters and then used the following calc fields to correct the data. 


            Val(If(Substr(,1,1)=","," ",[PRE_COLLECTIONS DEBIT]))


            Val(If(Substr(,1,1)=",",[PRE_COLLECTIONS DEBIT]+,[PRE_COLLECTIONS CREDIT]))


              -- Collections --  

              Debit      Credit