Extraction from Duplication

CFE4130

Forums

I'm working with a ton of medical claims data. I took claim data from 23 different offices and appened everything together. Then I ran the duplicate key using the recipients ID # (each patient's is unique) and the date of service. The result is 1.8 million duplicate claims. Also, each office has it's own unique billing number. From the duplicate results, they want me to take a specific office and find just the duplicates conencted to it. 
In the data there are also duplicates from the same day from the same office. They want to see all the ones that show the same date but both claims are from different offices. 
Any idea on how to obtain those results?

Brian Element Mon, 08/05/2019 - 14:34

Hi CFE4130 and welcome to the site.

So let me make sure I understand this:

Recipients ID is unique and related to a patient.  Could a patient have multiple IDs with different dates of service?

Each office has a unique ID also called the billing number, so you should have 23 unique numbers as you have 23 different offices?

So for the test for the duplicates by specific office you can do the following:

Duplicate key detection using the Recipient ID and the Office ID, you probably also want to look further with adding same transaction date, same amount, etc.  Maybe the first two and only same amount.  If you have billing codes maybe add that and looking for same receipient, same office and same billing code or procedure.  

For the second one you could use the Duplicate Key Exclusion in which the office ID is different but the other items are the same.

Doing duplicate key detection is almost an art form in that you have to look at what you have to work with, namely the fields and then look at ways there could be duplicates.  Generally you have to do several different permutations in order to get all potential duplicates.

Hopefully this helps you out a bit, if not let me know and we can discuss further.

Brian

CFE4130 Mon, 08/05/2019 - 15:09

Hi Brian,
Each patient will have their own ID, and it won't change like a social security number. 
I think I need to be a little more specific. I ran the duplicate key to show duplicates for each date of service and each office. The results showed  two or more claims for the same Recipient on the same day from the same office, and it also showed when there were two or more claims for the same Recipient on the same day but at different offices. 
From those results they want to me to get rid of the ones that show two or more claims from the same office, and just show the same day claims when they are from two different office locations. 
Also, they want to see duplicates from the Houston Office within the office and with other office locations. In the duplicate key detection that I have ran, would there be a way to pull out just the Houston office (using it's unique billing number) the duplicate that goes with it?
I'm hope I'm being clear

Brian Element Tue, 08/06/2019 - 14:59

Hi CFE4130,

For the same day claims from two different locations you can perform Duplicate Key - Exclusion with your duplicates being the ID, date and the different field being the location ID.  Now in there you might have duplicates where you could have two or more items in the same office with at least item in a different office, would you keep those as they are in two offices for the sample patient on the same day?

For the Houston office select duplicate key detection, the key would be the patient ID and the date.  In the Criteria filter on the Houston office, so your criteria might look like LOCATION == "001" if 001 was the Houston office.  This way the duplicates will only be performed within the Houston Location.

Hopefully this is answering your questions.

Brian

 

CFE4130 Wed, 08/07/2019 - 09:31

Hey Brian,
I appreciate you time on this. With the Houston office matches, it should show me when Houston office has the same paitent and date of service with the Austin office; not just within the Houston office.
Yesterday afternoon and currently I'm messing with Houston office matches. I forgot to mention that it's actually 6 Houston offices all with their unique billing number. Yesterday, I appended all of those offices together. Then I did another appened with all of the offices not in Houston. Then I Joined the two matching the Paitent ID and the Date of service. I believe I have that part solved now. I'm about to start messing with the next one.