Dealing with Duplicate/Reversing Data

[JDP5]

Hi,

I'm working with a large amount of pharmacy claims data and I am trying to remove all of the reversal entries as well as the original claim itself. However, I'm running into several different scenarios which is making it difficult to develop a method to accomplish this.

The scenarios I'm running into are...

1) A straightforward reversal - one charge in and one reversal charge out, both rows should be deleted.

2) An adjustment reversal - one charge in for an incorrect quantity, a reversal of that quantity, then another charge in for the correct quantity. The incorrect quantity and reversal of that quantity should be removed. The correct quantity should remain.

3) A duplicate reversal - two charges in and one reversal charge out, one of the charges in and the reversal charge should be removed, one charge in should remain.

Each of these scenarios can occur individually for a single patient or multiple/all scenarios can occur for a single patient.

Here's a list of fields I can work with:

MEMBER_FIRST_NAME (Self explanatory)
MEMBER_LAST_NAME (Self explanatory)
MEMBER_ID (Unique ID, consistent between claims of the same patient)
DATE_FILLED (Date prescription is filled)
DATE_SUBMITTED (Date data is entered into system, may have a lag from fill date)
PATIENTDOB (Self explanatory)
CLAIMNO (Unique claim number, this is unique across claims and reversals even for the same patient and does not appear to follow any pattern, but may have a sequential component to it; for the purposes of the scrubbed data this is random)
CLAIMTYPEID (Reversal/Claim)
AGE (Self explanatory)
DRUG_NAME (Self explanatory)
QUANTITY (Self explanatory)
NDC (Drug ID - better to use this than the drug name since the drug name may be slightly different based on how it is typed into the system. Even an extra space can make one drug look like multiple whereas the NDC will be consistent.)

These are the fields I've created from the original data set to isolate these situations and help me work through the data:

Unique ID
Matched Reversal (Reversal with a charge in, this did not consider if the charge in was the same quantity; TRUE/FALSE)
Matched Reversal > 1 (Check for sum quantity of reversals that are greater than 1 to assist in identifying scenario #2 and #3 above; TRUE/FALSE)
Keep/Remove (Desired outcome)
Scenario (Identifier to match data with the aforementioned scenarios above)

I hope this provides enough information for any one capable of assisting. I'll also attach a sample workbook with scrubbed data to work with.

If anything else is needed to assist, please let me know.

Thanks,

Joe