Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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.

1 Solution

Accepted Solutions
sunny_talwar

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;


Capture.PNG


View solution in original post

4 Replies
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

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;


Capture.PNG


Anonymous
Not applicable
Author

worked great, thank you

Anonymous
Not applicable
Author

worked great.  Thank you!