Sum column value based on another column values

I have a scenario where my datatable is as below

HOUR VALUE
1 1
2 2
3 7
4 3
5 25
6 52
7 771
8 23045
9 34395
10 44739

I want to convert the above table as below

HOUR VALUE
Before 5 38
6 52
7 771
8 23045
9 34395
10 44739

I want to Sum first 5 values and write it in the table.

How to achieve this?

create a clone of the datatable you have read or create a datatable with same schema. Create a int variable and intialize to 0, intSum = 0

then you can use a for each row loop and also have a variable created for the index of that for each data row loop

have a if condition that will check if the index variable is less than 5
If it is less than 5 then add the require value in the int variable
intSum = intSum + Cint(row("Value"))

this will sum the first 5 rows’ value

in then block
add the if condition to check if index is 4
if it is 4 then add the row in the clone/build datatable by using Add Data Row
and an array that is like {"SumOf5",intSum.toString}
in the else block simply add the Add Data Row Activity and put row variable as row (the loop variable of the foreach data row) and this will add the remaining rows in the clone datatable

Hope this helps!

1 Like

Thank you very much, this helps.

1 Like