Claims Data Investigation

[George_Fischetti]

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!

[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 · Accepted solution]

Hi @George_Fischetti 

Here is how you can do it.

Workflow:

Output:

Hope this helps : )

[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!

[ECO]

Hi, can we connect mainframe to alteryx? do you have step by step process? thanks in advance