Data Grooming with Mistakes in Data

[jrdnjhnsn2]

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?

[RolandSchubert · Accepted solution]

Hi @jrdnjhnsn2 ,

I think, the most important step is to convert "effective_date" to a "DATE" data type to enable sorting. As a next step (after sorting), you have to find the last status, and add it to the records. Then Multi-Row formula tool should do the job.

I've attached a sample workflow. Let me know if it works for you.

Best,

Roland

[jrdnjhnsn2]

Thanks @RolandSchubert! This is working perfectly, and is much more succinct than what I was working on.