Using value of column in previous row in formula in current row

1

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

2

Hi Keith,

Iterations are not required here. Instead, mark the last row in each group, calculate a cumulative sum of assigned hours, and then modify Assigned_hrs using the following rule:

IF the current row is the last row in the group AND pct = 0 THEN replace Assigned_hrs with the remaining difference between cumulative hours and total hours OTHERWISE keep Assigned_hrs as is.

In the below project we calculate row numbers inside a group in one column, and then the max number per group in the other column. When the two numbers are equal it marks the last row in a group. The final expression is:

IF [Count in group] = [Max in group] //The last row in group.
THEN [hrs] - [Cumulative_assgined]
ELSE [Assigned_hrs]

group-last-row.morph (6.3 KB)

image
image1079×620 20.2 KB

1 Like