- Oct
- 364
- 17
No code here, we do this using Excel VBA, but the same approach could be done in TCC.
Scenario: Hospital records are downloaded as pipe-delimited single-line text records that contain patient and "guarantor" contact info, account number, date of admission and discharge, amount of charges, etc. These are for people involved in MVAs (Motor Vehicle Accident). A typical download might be 90 accounts.
In hospital-industry jargon "guarantor" means "anyone other than an insurer who is financially responsible for the bill". So the patient can also be the guarantor, if the patient is a minor the parent could be a guarantor, etc.
Problem: Often there are "related accounts". These could be other people in the same accident, and/or more than one bill, e.g., from a follow-up visit. Those all need to be sent to a single insurer, such as the liability insurer of the person who caused the accident.
Consider 3 people in the same MVA -- maybe with different names, e.g., Jane Maggers (driver) , Mary Garcia (Jane's sister, passenger), Bill Jones (Mary's live-in boyfriend, and a passenger).
So, obviously, "same name" won't work. Even with one person name variations are common, e.g., Mary Lou Garcia in one record and Mary Garcia in another--and maybe they don't have correct birthdate and SSN info.
We tried looking for close account numbers but ran into this: Jane's account is 005 and Harry Fritz's is 007. Yes, probably related. But people in one car usually don't remember the names of folks in other vehicles. So we stopped using that.
First, we sequence records by Guarantor's Street Address. You can't do a direct "If A EQ B". The problem is there are too many variations, e.g., 123 North Place Apt 345 and 123 North Place #345. Solution: Compare everything up to the first space plus the next two characters. That catches over 95% of "same address".
Yes, it would consider 123 North Place and 123 Nontum Court as "equal". But that winds up extremely rare.
Caveat: 123 E 67TH ST evaluates as 123^67. ("What? How can an address be "out of range" ??") So I had to make sure it considered it as STRINGS.
For the remainders, next look at Guarantor Surname. (e.g., if Jane were the Guarantor for two children all 3 records would show the same Guarantor, even if they lived at different addresses.)
Together these pull over 98% of "relateds". Just to be extra careful, compare Patient Surname to spot any remaining "stragglers".
Finally (because this is coded), for the accounts that have been flagged, sort by Account Number and check if the Account Numbers are within 6 (arbitrary number). If so, include them in a set separated by single CR/LF's. End each group with a blank line. If not "close", include them in the list anyway as a one-line "group". Particularly with return visits, the first and second numbers will not be close--but addresses will match, etc.
Scenario: Hospital records are downloaded as pipe-delimited single-line text records that contain patient and "guarantor" contact info, account number, date of admission and discharge, amount of charges, etc. These are for people involved in MVAs (Motor Vehicle Accident). A typical download might be 90 accounts.
In hospital-industry jargon "guarantor" means "anyone other than an insurer who is financially responsible for the bill". So the patient can also be the guarantor, if the patient is a minor the parent could be a guarantor, etc.
Problem: Often there are "related accounts". These could be other people in the same accident, and/or more than one bill, e.g., from a follow-up visit. Those all need to be sent to a single insurer, such as the liability insurer of the person who caused the accident.
Consider 3 people in the same MVA -- maybe with different names, e.g., Jane Maggers (driver) , Mary Garcia (Jane's sister, passenger), Bill Jones (Mary's live-in boyfriend, and a passenger).
So, obviously, "same name" won't work. Even with one person name variations are common, e.g., Mary Lou Garcia in one record and Mary Garcia in another--and maybe they don't have correct birthdate and SSN info.
We tried looking for close account numbers but ran into this: Jane's account is 005 and Harry Fritz's is 007. Yes, probably related. But people in one car usually don't remember the names of folks in other vehicles. So we stopped using that.
First, we sequence records by Guarantor's Street Address. You can't do a direct "If A EQ B". The problem is there are too many variations, e.g., 123 North Place Apt 345 and 123 North Place #345. Solution: Compare everything up to the first space plus the next two characters. That catches over 95% of "same address".
Yes, it would consider 123 North Place and 123 Nontum Court as "equal". But that winds up extremely rare.
Caveat: 123 E 67TH ST evaluates as 123^67. ("What? How can an address be "out of range" ??") So I had to make sure it considered it as STRINGS.
For the remainders, next look at Guarantor Surname. (e.g., if Jane were the Guarantor for two children all 3 records would show the same Guarantor, even if they lived at different addresses.)
Together these pull over 98% of "relateds". Just to be extra careful, compare Patient Surname to spot any remaining "stragglers".
Finally (because this is coded), for the accounts that have been flagged, sort by Account Number and check if the Account Numbers are within 6 (arbitrary number). If so, include them in a set separated by single CR/LF's. End each group with a blank line. If not "close", include them in the list anyway as a one-line "group". Particularly with return visits, the first and second numbers will not be close--but addresses will match, etc.