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?

       

      Thanks,

      Corey

      /QUOTE

       

      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:

          if(x2="-",

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

          val(x1)*10000+Value1,Value1),

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

          val(x1)*10000+Value1,

          Value1))[/code]

           

          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  

            -


                                

                     1,065,608.50

                       213,294.94

               137.36-           

            1,874.01            

                        68,491.10

                              .33

            /code