2 Replies Latest reply: May 15, 2014 10:08 AM by guyporter _ RSS

    Changing negatives

    guyporter _

      Hi

      I have a problem with extracts that come up from time to time. I am using Monarch to extract aging data to then upload to another system. The other system will not accept negative characters and relies on a letter to convert negative values to the right amounts.

      So an invoice of £100 is imported as a positive £100. A credit for £100.00 on a report shows as -£100.00 and is imported as a code C (for credit) £100 and the system then converts this to a negative.

      We successfully use a formula

      If(I="P".or.I="C".or.I="A",-(A),A)

      to convert negatives to positives and use a look up to get the codes so a payment is a P and so on.

      this all works fine until the client in their wisdom mixes a credit as both a positive and a negative value so some credits are positive and some negative. The above formula converts the negatives to positives (as it should) but then converts the positives to negatives so the upload then fails

      What I am looking for is two formuals that will do similar to the above does the following:-

       

      Say

      Look up code =J  Some J's are pos and some neg

      So if Code J = a positive then the = JP

      or if Code J is a negative the it equals JN

       

      I can then tell a further lookup (I do formulas in simple stages or I get hopelessly lost) to show a JP as an invoice and an JN as a credit. The original formula

       

      If(Item type="JN".or.Item Type="C".or.Item Type="A",-(Amount),Amount)

      and I will get the right number to enter into the system

      Date      Type                    Amount1 Amount 2

      220710     J          -147.87     147.87

      220710     J          -29.38     29.38

      220710     J          -16.33     16.33

      230710     J          0.8     -0.8

      230710     J          143.76     -143.76

      270710     J          79.22     -79.22

      280710     J          5.58     -5.58

      040810     J          4.7     -4.7

       

      If you can make sense of this and help I would be grateful. IF there is a better way of doing this then even better. I would appreciate a formual that I can follow.

      Thanks

      Guy

        • Changing negatives
          Data Kruncher

          Assuming that codes and values from the report are represented by PreCode and PreValue, then two formulas will get this result:

           

          PreCode    PreValue    Code    Value

          J              1000    JP       1000

          J             -1000    JN       1000

          P             -1000    P        1000

          C             -1000    C        1000

          A             -1000    A        1000[/FONT]

          /codeCode:

          If(PreCode='J';

          if(PreValue<0;'JN';'JP');

          PreCode)[/code]and Value:

          Abs(PreValue)[/code]

           

          Does this represent your logic accurately?