Include new field with subtotals/status in load script

[hansdevr]

Hi Everyone,

I have a table, which includes Claim#, Date of payment and amount payed.

I would like to add a field, which adds up all the payments for a certain claim, and puts the results as an "Amount payed upto now" in a separate field, which I would like to call "Sub". See example below. How would I go about this in the load script?

Any help is very welcome!

Hans

Claim#	Payment date	Amount	Sub
10001	1-6-2004	€ 40,00	€ 40,00
10003	15-4-2016	€ 100,00	€ 900,00
10003	10-4-2016	€ 600,00	€ 800,00
10003	1-4-2016	€ 200,00	€ 200,00
10006	11-5-2016	€ 500,00	€ 10.500,00
10006	1-5-2016	€ 10.000,00	€ 10.000,00

[sunny_talwar]

May be like this:

Table:

LOAD Claim#,

          [Payment date],

          Amount

FROM Source;

Left Join (Table)

LOAD Claim#,

          Sum(Amount) as Sub

Resident Table

Group By Claim#;

[hansdevr]

Hi Sunny, fast as lightning you are!!

I may be wrong, but does this take the order of dates in account? Should I add order by Claim#, Date ASC

[sunny_talwar]

I think the above is not going to give what you are looking for. Working on an alternate solution

[Kushal_Chawda]

try this

Table:

LOAD Claim#,

          [Payment date],

          Amount

FROM Source;

New:

noconcatenate

LOAD *,

          if( Claim# =previous(Claim#), Rangesum(Amount,peek('Sub')),Amount) as Sub

Resident Table

order by Claim#,[Payment date] ;

drop table Table;

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD * INLINE [

    Claim#, Payment date, Amount

    10001, 1-6-2004, 40

    10003, 15-4-2016, 100

    10003, 10-4-2016, 600

    10003, 1-4-2016, 200

    10006, 11-5-2016, 500

    10006, 1-5-2016, 10000

];

FinalTable:

LOAD *,

  If(Claim# = Previous(Claim#), RangeSum(Amount, Peek('Sub')), Amount) as Sub

Resident Table

Order By Claim#, [Payment date];

DROP Table Table;

[hansdevr]

Hahahaha, Sunny and Kushal have the exact same solutions!!

How do I mark BOTH answers as being correct?!?

Thnx, guys, you've both been a great help!!

[Kushal_Chawda]

Again I think there will be fight for correct answer mark

[Kushal_Chawda]

Please check the reply time.. who replied first mark that as correct

[sunny_talwar]

Tu le le bhai correct answer . Waise the reply time changes based on modifications made to the posts. I added the sample after my intial response