How to make tables with derived values

[freddean]

Hi all,

I am trying to do what I thought to be quite simple but after trying to find the resources to answer the question myself I still haven't managed to get it.

I have a date, and an amount of money spent on that date. This money comes out of a pre-allocated amount of money. What I need to do is get a rolling sum of the money left in the pre-allocated amount on a day by day basis. If the amount is depleted I need to allocate the same amount again to the rolling sum, and count how many times in a month a re-allocation has to be done.

I've done it in Excel which was quite easy, I just had an if statement that said
IF CurrentAccBalance - DailySpend < 0 THEN field=CurrentAccBalance - DailySpend + 1,000,000 ELSE  field=CurrentAccBalance - DailySpend

Example below:

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD * INLINE [
    day, amount_spent
    0, 0
    1, 650000
    2, 500000
    3, 420000
    4, 45000
    5, 750000
    6, 280000
    7, 2000
    8, 25000
];

FinalTable:
LOAD *,
	 //RangeSum(Peek('cum_amt_spent'), amount_spent) as cum_amt_spent,
	 If(day = 0, 1000000, If(RangeSum(Peek('Account'), -amount_spent) < 0, RangeSum(Peek('Account'), -amount_spent, 1000000), RangeSum(Peek('Account'), -amount_spent))) as Account,
	 If(day = 0, 1, If(RangeSum(Peek('Account'), -amount_spent) < 0, RangeSum(Peek('Bucket_No'), 1), Peek('Bucket_No'))) as Bucket_No
Resident Table
Order By day;

DROP Table Table;