4 Replies Latest reply: May 9, 2017 4:52 PM by Tammy Knezevich RSS

    Scripting question about subtracting amount from columns until 0

    Tammy Knezevich

      How can I accomplish this in my script?  I need the final results to be stored in a table because I will no longer need ther "original" amounts. I will use the 'final' amounts in all of my charts in m y application

       

      I am loading data from a claims file:

       

      ClaimId,

      NegGapAmt,

      PaymentOwedonLoan AS A

      PenaltyOnLoan AS B,

      PriorDamage AS C,

      OverDeductible AS D,

      SalvageValue AS E,

      Other1 AS F,

      Other2 AS G

      FROM claims

       

      The ClaimID is the id

      The NegGapAmt is the total $ that I need to allocate out to the remaining 7 columns (A-G) based on the highest dollar amount to the lowest.

      For example:

      ORIGINAL DATA IN TABLE



      ClaimIdABCDEFGNegGapAmt
      1237300800100002002000
      383758986781000000500
      23400010000001000
      123420020001500020010003000

       

      RESULTS:



      ClaimidABCDEFGNegGapAmt
      12371000002000
      3837539867810000000
      23400000000
      123420000500020010000

      Claimid-1237

      1000 was subtracted from C leaving 0 in C, then 800 subtracted from B leaving 0 in B then 200 from A leaving 100 in A.   At this point NegGapAmt = 0, therefore nothing else to subtract.

      Claimid-38375

      $500 was subtracted from B leaving 678 in B,  At this point NegGapAmt = 0 ,all other columns are not effected.

      Claimid -234

      $1000 was subtracted from D leaving 0 in D.  At this point NegGapAmt = 0, all other columns are not effected.

      Claimid -1234

      $2000 was subtracted from B leaving 0 in B, 1000 subtracted from C leaving 500 in C. At this point NegGapAmt = 0.  All other columns not effected.