Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table with account id, bill date and bill amount with historical data. How do I add a new field called cumulative amount which will add amount from previous months ad current bill amount (Running Total). This needs to be grouped by account id.
Thank you
Hi
something like that
Temp:
load
rowno() as Rowkey,
Date,
Account_Id,
bill_amount
from your source;
Final:
load
*
if(Rowkey=1,bill_amount, if(peek('Account_Id')<> Account_Id,bill_amount, bill_amount +peek('bill_amount_cumul') ) ) as bill_amount_cumul
resident Temp order by Date,Account_Id ;
drop table Temp;
Hi
something like that
Temp:
load
rowno() as Rowkey,
Date,
Account_Id,
bill_amount
from your source;
Final:
load
*
if(Rowkey=1,bill_amount, if(peek('Account_Id')<> Account_Id,bill_amount, bill_amount +peek('bill_amount_cumul') ) ) as bill_amount_cumul
resident Temp order by Date,Account_Id ;
drop table Temp;
Yeah I tried this but it doesn't seem to work. I changed the order by as account, date and it worked ! Thank you !