Scripting question about subtracting amount from columns until 0

[Anonymous]

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

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

RESULTS:

Claimid	A	B	C	D	E	F	G	NegGapAmt
1237	100	0	0	0	2	0	0	0
38375	398	678	100	0	0	0	0	0
234	0	0	0	0	0	0	0	0
1234	2000	0	500	0	200	100	0	0

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.

[marcus_sommer]

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

[sunny_talwar · Accepted solution]

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:

LOAD Key,

  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:

LOAD DISTINCT Key,

  ClaimId,

  Diff as NegGapAmt

Resident TempTable;

FOR i = 1 to FieldValueCount('Column')

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

  Left Join (FinalTable)

  LOAD DISTINCT Key,

  ClaimId,

  Diff as NegGapAmt,

  NewValue as [$(vField)]

  Resident TempTable

  Where Column = '$(vField)';

NEXT

DROP Table TempTable;

[Anonymous]

worked great, thank you

[Anonymous]

worked great.  Thank you!