Script for sum of all previous rows for a column , not the current row

[prahlad_infy]

Hello All ,

I have a requirement to achieve accumulation of previous rows through script . 

New field should be created at script level using Measure column . 

I tried range sum , but it not helping . Its basically accumulating current + previous value . Which I not my Requirement .

Ex - If a person selects Day3 - then New_Field should be sum measure of Day1+Day2 only 

Client	Day	Measure	New_field	Formula for New_Field
Client1	Day1	10	0	0
Client1	Day2	20	10	10
Client1	Day3	30	30	10+20
Client1	Day4	40	60	10+20+30
Client1	Day5	50	100	10+20+30+40
Client1	Day6	70	150	10+20+30+40+50
Client1	Day7	70	220	10+20+30+40+50+70
Client1	Day8	80	290	10+20+30+40+50+70+70
Client2	Day1	10	0	0
Client2	Day2	20	10	10
Client2	Day3	30	30	10+20
Client2	Day4	40	60	10+20+30
Client2	Day5	50	100	10+20+30+40
Client2	Day6	88	150	10+20+30+40+50
Client2	Day7	70	220	10+20+30+40+50+88

LOAD * INLINE [

Client, Day, Measure
Client1, Day1, 10
Client1, Day2, 20
Client1, Day3, 30
Client1, Day4, 40
Client1, Day5, 50
Client1, Day6, 70
Client1, Day7, 70
Client1, Day8, 80
Client2, Day1, 10
Client2, Day2, 20
Client2, Day3, 30
Client2, Day4, 40
Client2, Day5, 50
Client2, Day6, 88
Client2, Day7, 70
];

Thank You

[olofmalmstrom]

Hi, 

You need to peek the previous row to check if it is the same Client and the peek the previous row to sum a total, and ensure that you get the rows in correct sort order 🙂 

See code below

data:

LOAD * INLINE [

Client, Day, Measure

Client1, Day1, 10

Client1, Day2, 20

Client1, Day3, 30

Client1, Day4, 40

Client1, Day5, 50

Client1, Day6, 70

Client1, Day7, 70

Client1, Day8, 80

Client2, Day1, 10

Client2, Day2, 20

Client2, Day3, 30

Client2, Day4, 40

Client2, Day5, 50

Client2, Day6, 88

Client2, Day7, 70

];

data2:

load *,

if(Client = peek(Client), peek(Total)+Measure,Measure) as Total 

resident data

Order by Client ASC, Day ASC;

drop table data;

[prahlad_infy]

For Client1 on Day3 it should be - 10+20 , 

Day 2 - 10 

[martinpohl]

Hi,

use this script:

Temp:

LOAD * INLINE [

Client, Day, Measure

Client1, Day1, 10

Client1, Day2, 20

Client1, Day3, 30

Client1, Day4, 40

Client1, Day5, 50

Client1, Day6, 70

Client1, Day7, 70

Client1, Day8, 80

Client2, Day1, 10

Client2, Day2, 20

Client2, Day3, 30

Client2, Day4, 40

Client2, Day5, 50

Client2, Day6, 88

Client2, Day7, 70

];

// in case that datas are not ordered, order in script

sortedby:

NoConcatenate load

Client,

Day,

Measure

resident Temp

order by Client, Day;

drop table Temp;

Cumulated:

load

Client,

Day,

Measure,

if(Client=peek(Client,-1),

if(Client=peek(Client,-2),rangesum(peek('Measure',-1),peek('Measure_kum',-1)),

peek('Measure',-1)

  )

,0) as Measure_kum

resident sortedby;

drop table sortedby;

In your example there is an error, 150+88 = 238, not 220

Regards

[marcus_sommer]

It might be done with:

load *, if(Client = previous(Client), peek('New') + Measure, 0) as New
resident Source order by Client, Day;

[olofmalmstrom]

@prahlad_infy  Small change that will create what you asked for

data:

LOAD * INLINE [

Client, Day, Measure

Client1, Day1, 10

Client1, Day2, 20

Client1, Day3, 30

Client1, Day4, 40

Client1, Day5, 50

Client1, Day6, 70

Client1, Day7, 70

Client1, Day8, 80

Client2, Day1, 10

Client2, Day2, 20

Client2, Day3, 30

Client2, Day4, 40

Client2, Day5, 50

Client2, Day6, 88

Client2, Day7, 70

];

data2:

load *,

if(Client = peek(Client), peek(Total)+peek(Measure),0) as Total 

resident data

Order by Client ASC, Day ASC;

drop table data;