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

# Scripting question about subtracting amount from columns until 0

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.

• ###### Re: Scripting question about subtracting amount from columns until 0

I think I would at first transform the data with The Crosstable Load. In a following sorted resident load I would use the interrecord-functions Peek() or Previous() ? to subtract the values against NegGapAmt until the difference is zero. And only if you really need a final crosstable-structure I would transform it again with The Generic Load.

- Marcus

• ###### Re: Scripting question about subtracting amount from columns until 0

worked great, thank you

• ###### Re: Scripting question about subtracting amount from columns until 0

May be like this

Table:

CrossTable (Column, Value, 4)

LOAD RecNo() as Key,

ClaimId,

NegGapAmt,

RangeMax(RangeSum(NegGapAmt, -A, -B, -C, -D, -E, -F, -G), 0) as Diff,

A,

B,

C,

D,

E,

F,

G;

LOAD * INLINE [

ClaimId, A, B, C, D, E, F, G, NegGapAmt

1237, 300, 800, 1000, 0, 2, 0, 0, 2000

38375, 898, 678, 100, 0, 0, 0, 0, 500

234, 0, 0, 0, 1000, 0, 0, 0, 1000

1234, 200, 2000, 1500, 0, 200, 100, 0, 3000

];

TempTable:

ClaimId,

Column,

Value,

Diff,

NegGapAmt,

If(Key = Previous(Key), RangeMax(Peek('NewNegGapAmt') - Peek('Value'), 0), NegGapAmt) as NewNegGapAmt,

RangeMax(Value - If(Key = Previous(Key), RangeMax(Peek('NewNegGapAmt') - Peek('Value'), 0), NegGapAmt), 0) as NewValue

Resident Table

Order By Key, Value Desc;

DROP Table Table;

FinalTable:

ClaimId,

Diff as NegGapAmt

Resident TempTable;

FOR i = 1 to FieldValueCount('Column')

LET vField = FieldValue('Column', \$(i));

Left Join (FinalTable)

ClaimId,

Diff as NegGapAmt,

NewValue as [\$(vField)]

Resident TempTable

Where Column = '\$(vField)';

NEXT

DROP Table TempTable;

• ###### Re: Scripting question about subtracting amount from columns until 0

worked great.  Thank you!