Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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#;
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
I think the above is not going to give what you are looking for. Working on an alternate solution
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;
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;
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!!
Again I think there will be fight for correct answer mark
Please check the reply time.. who replied first mark that as correct
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