How to find Records that don't match

[Andy_Katona]

Team,

I have 100K records that I am matching on valid HEDIS ICD & CPT codes. I need to review those that don't match.

Thoughts on how I would do that?

Currently I take the Raw data to bounce to our DB where the valid HEDIS codes are located. Then re-merge the files back. I have a fallout of 500 records that didn't have valid ICD or CPT codes that met the HEDIS measures. I need to see those 500 records.

Thank you for your help.

[NPT · Accepted solution]

Hi @Andy_Katona,

It sounds like you can use the JOIN tool and take the "Right Unjoined" output (as long as the raw data stream is coming from the Left Input and the valid codes are on the Right Input).  The Right Unjoined stream would be all the records that didn't match the Left Input on the fields you specified in the Configuration pane. 

Join Tool is on the "Join" menu:

All the best,

~ Nathan

[Andy_Katona]

I had tried that thus I must have done something wrong. I need to ensure the MemberKey, the ICD, & The DOS don't match. We can have records that can have  10 memberkeys entries but different ICD codes & DOS because each value has to be entered in for HEDIS.

Thus I would need to perform the following

E.G.

Record 1: Member 1234, DOS, 1/1/2017, ICD Z6831 AND CPT <blank>

Record 2: Member 1234, DOS, 1/1/2017, ICD <blank> AND CPT 12345

These 2 records did not match either the ICD of Z6831 or the CPT 12345 or DOS

[charity_wilson]

Can you share the Workflow, or the data?

Something I've used in cases like this is the Unique tool.  It allows me to specify which fields have to be identical.  It then "filters" my data into two data stream, one that has duplicates, and one that has unique data.  It can be set to use multiple fields, so you can find only those records that match in all areas.  It's in the blue area, "Preparation."

[SGolnik]

Don't under estimate the power of splitting the data multiple ways, joining it multiple ways, and bringing it all back together. I am working on a workflow now where I am trying to match 837 claim files to claims in our core system...I joined one component at a time on different workflows to isolate the combinations and then joined them all back into one big workflow. 

[Andy_Katona]

This did work for me after I re-ran & pulled data to excel to validate. I was just missing something I guess. THANK YOU !!!!!!

[NPT]

Awesome Andy, that's great to hear!