Update values based on date in another row

Question

I've got some historical policy data, and I need to fix a couple of errors in it.

The part I'm struggling with is what to do with records 39 and 40. We're going to treat the date the change from coverage_status 1 to coverage_status 4 happened as 6/29/2009, which can be found in row 41 in the transaction_date column. I want to have have a period_start and period_end column for the records in rows 39 and 40. For record 39, I want the period_start to be 4/1/2009, and the period end to be 6/28/2009, and for record 40, I want the period_start to be 6/29/2009, and the period_end to be 6/30/2009.

Since these dates are in different rows, I'm having a hard time getting this to work systematically for different scenarios.

I've attached my starting point, and my desired output is included as well.

Any ideas how this can be done?

Community Example.yxdb

Starting point.yxdb

MattBSlalom · Accepted Answer

You're looking for the Multi-Row Formula tool.  In this sample, I had to convert a couple of your fields to an actual Date datatype (hence the field names with the "2" suffix), then have added 2 Multi-Row Formula tools to generate the Start & End dates.  You didn't specify where the April 1st value came from for row 39, so I just assumed the Min_PU_Date; this can easily be swapped with the effective_date if that was the desired source field.

Sample workflow:

The formula for the Period Start date is:

IF [New_Coverage_Status] != [Row+1:New_Coverage_Status]
THEN [Min_PU_Date2]
ELSEIF [New_Coverage_Status] != [Row-1:New_Coverage_Status]
THEN [Row+1:transaction_date2]
ELSE Null()
ENDIF

The formula for the Period End date is:

IF [New_Coverage_Status] != [Row+1:New_Coverage_Status]
THEN DateTimeAdd([Row+2:transaction_date2], -1, "day")
ELSEIF [New_Coverage_Status] != [Row-1:New_Coverage_Status]
THEN DateTimeAdd([Row+1:transaction_date2], 1, "day")
ELSE Null()
ENDIF