Hey everyone,
I've got some policy info that has some errors in it, and I'm trying to correct those errors. In my attached dataset, I've got a column called "coverage_status" which can take on values of 1-3. The rule in our data is once a policy has a coverage status of 3, then it can go to a 2, but it cannot go back to a 1. So, if there are 1's after the first 3, then we treat those 1's as errors and we'd like to change them to a 3. This seems like a great candidate for the multi-row tool; however, we also want to treat the most recent record for each policy as the correct record. I know that logic is kind of flawed, but that's how we're doing it for now. So if the last coverage status is a 1, then any 3 that appears before would be considered an error, and we want to change that to a 1.
Similarly, a 2 cannot go back to a 1. If the last record for a policy is a 2 but there are several 1's after a previous 2, then we'd want to change those 1's to a 2 like this:
| coverage_status | effective_date | policy_number | Desired coverage_status |
| 2 | 1/1/2002 | A | 2 |
| 1 | 4/1/2002 | A | 2 |
| 1 | 7/1/2002 | A | 2 |
| 2 | 10/1/2002 | A | 2 |
If the last record is a 2 and there were no previous 2's, then we just leave it as is.
For what it's worth, a 1 means "active," and a 2 means "terminated." A 3 is still active, but it's treated differently than a 1.
I've tried several different iterations of sorting the data on effective data and using the multi-row tool to catch these errors, but I pretty quickly ran into issues because I can't say "look n rows into the future to see how to treat this rows status."
Any ideas on how to tackle this?
