Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

tknezevich
New Contributor

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.

Tags (1)
1 Solution

Accepted Solutions

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:

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


4 Replies
MVP & Luminary
MVP & Luminary

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

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


tknezevich
New Contributor

Re: Scripting question about subtracting amount from columns until 0

worked great, thank you

tknezevich
New Contributor

Re: Scripting question about subtracting amount from columns until 0

worked great.  Thank you!