SUM WITH PREVIOUS ROW FOR EACH NAME

[entsh]

hi

please see the table 1

ROW	NAME	NUMBER	1-SUM (SUM WITH PREVIOUS ROW FOR EACH NAME)FROM SMALLEST ROW TO LARGEST ROW NUMBER
1	A	500	=500
4	A	450	450+500=950
8	A	550	950+550=1500
10	A	1000	1500+1000=2500
15	A	500	2500+500=3000
3	B	400	=400
5	B	600	=400+600=1000
42	B	700	1000+700=1700

RESULT

NAME	RESULT (TOTAL SUM)
A	3000
B	1700

I WANT THE FORMULA FOR 1-SUM COLUMN AND 2-RESULT TABLE

THX

[mato32188]

Hi @entsh ,

if you have not sorted your table yet, please do the following in script:

tmp:

Load 

ROW,

NAME,

NUMBER

from xxx;

noconcatenate

tmp1:

load * resident tmp order by NAME; drop table tmp;

tmp2:

load

ROW,

NAME,

NUMBER,

if(previous(NAME)=NAME, rangesum(NUMBER,peek('NUMBER_ACC')),NUMBER) as NUMBER_ACC

Resident tmp1;

drop table tmp1;

Then you can simply sum the NUMBER_ACC in any object you want.

BR

m

[Saravanan_Desingh · Accepted solution]

Try this,

tab1:
LOAD * INLINE [
    ROW, NAME, NUMBER
    1, A, 500
    4, A, 450
    8, A, 550
    10, A, 1000
    15, A, 500
    3, B, 400
    5, B, 600
    42, B, 700
];

tab2:
NoConcatenate
LOAD *, If(Previous(NAME)=NAME, RangeSum(NUMBER,Peek('NUMBER_ACC')),NUMBER) As [1-SUM]
Resident tab1
Order By NAME, ROW;

Left Join(tab2)
LOAD NAME, Sum([1-SUM]) As RESULT
Resident tab2
Group By NAME;

Drop Table tab1;

[Saravanan_Desingh]

Output: