Claims Data Investigation

Hi Folks - I'm trying to simplify a process where I'm using a join to highlight mismatched records between a claims file and mainframe file. There is a many to many matching condition here so I'm trying to make the records unique but using 'policy # + Claim #'

We are trying to identify situations where information in the claims system is not matching the information in the mainframe system, specifically the customer Phone #, customer email, and customer bank account.

Is there a better way to analyze this data?? The sample data I've attached will highlight the testing we're trying to accomplish. Any help is greatly appreciated. I'm still an Alteryx rookie so looking to understand how others approach such issues.

Thanks!

ClaimsVsMainframe-AlteryxCommunitySample.xlsx

Common Use Cases

Accepted answers

atcodedog05

Hi @George_Fischetti

Here is how you can do it.

Workflow:

Output:

Hope this helps : )

match.yxmd

Match Report.xlsx

All comments

Zas3nfkb

Assuming the fields match, you could union the datasets, then use a group by whatever you are checking and use the count function. If count <2 you know its a mismatch for that record. This would probably be more efficient from a processing standpoint. You can do this for each field you are checking or if you wanted to check all 3 fields, then group by all 3 fields and check.

atcodedog05

Hi @George_Fischetti

Here is how you can do it.

Workflow:

Output:

Hope this helps : )

match.yxmd

Match Report.xlsx

George_Fischetti

Thank you @atcodedog05, @Zas3nfkb - appreciate your input. We still have an issue with the amount of records in these files which is why I was trying to make the records more unique but concatenating the Policy # and Claim #, the join will likely break down and not execute fully. We have reached out to our source to rework the data a bit, but wanted to try something while we waited. This will work, just a matter of fine tuning the data.

Thanks again for your help and learning opportunity!

atcodedog05

Happy to help : ) @George_Fischetti

Cheers and have a nice day!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703