topic Re: Scripting question about subtracting amount from columns until 0 in QlikView

Scripting question about subtracting amount from columns until 0

Anonymous — Mon, 08 May 2017 17:40:47 GMT

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	NegGapAmt
1237	300	800	1000	0	2	0	2000
38375	898	678	100	0	0	0	500
234	0	0	0	1000	0	0	1000
1234	200	2000	1500	0	200	100	3000

RESULTS:

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

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

marcus_sommer — Mon, 08 May 2017 18:56:00 GMT

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

sunny_talwar — Mon, 08 May 2017 19:14:27 GMT

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,

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;

Re: Scripting question about subtracting amount from columns until 0

Anonymous — Tue, 09 May 2017 20:52:55 GMT

worked great, thank you

Re: Scripting question about subtracting amount from columns until 0

Anonymous — Tue, 09 May 2017 20:53:45 GMT

worked great. Thank you!