I am processing a table that is grouped by two data elements and it has 3 columns I need to use in calculations. One of the columns needs to be updated with the value of a resulting calculation from the previous row within the group.

Example

Input table:

Key1,Key2,hrs,pct

DE1, DE2, 100, .50

DE1, DE2, 100, .10

DE1, DE2, 100, 0.00

DE1, DE3, 1, .40

DE1, DE3, 1, .60

Needed calculations:

Assigned_hrs = if(hrs = 1,1,hrs * pct)

updated_hrs = if(hrs=1,1,round(hrs - assigned_hrs,2))

These work just fine - The issue I am having is that for row 3 in the example I need the calculation to be: Assigned_hrs = 100 - (Row 1 Assigned_hrs + Row 2 Assigned_hrs) because that row is the last in the grouping and because the pct = 0 it gets the remaining hrs, if any.

I have tried to iterate through it , shift down through it and for the life I me I can’t get it to work.

Any help would be greatly appreciated.

Thanks,

Keith