Replace Record Id with Group Id when joining back to original data

[gbruschi]

Hi All,

I still very new to Alteryx and need your help!  I created a grouping after fuzzy matching and now I need to replace all record ids for those that have a group id with the group id and retain a record id for those without a grouping.

I have attached an excel file which shows the outcome i am expecting and my sample workflow as well.

I'll appreciate any help in helping me solve this problem.

Thank you!!

Gbemi

[jrgo · Accepted solution]

@gbruschi,

I modified your workflow (attached) with what i believe you're trying to get to. It doesn't match up to your "Final Summary" table, but because you were summing the records that were derived from the same source. The way you had your Fuzzy Match tool configured was to do a merge, which compares only values to other source records. In that same tool, I also updated the option for "Source ID Field" dropdown. It was RecordID was selected here, which would consider ever record as it's own source. You had already created a "source" field so assuming this is what you were intending to use it for. Last thing i changed... the "Advanced Options" section, I checked the option to output unmatched records.

 

Other than the changes in the FM tool, the rest of the changes should be easy to identify. 

Hope this helps!

Jimmy

[gbruschi]

Hi Jimmy,

This is awesome! thank you very much.

In regards to the records from the same source, I could summarize at the end using the last names and/or address to get household level data which is what i need in this case. 

I am wondering though how you would suggest I handle the names/duplicates from the same source.  Is best practice to first purge each data file before merging and then going through the fuzzy matching process or using the unique tool after the merge?

Again thank you for your help!

Gbemi

[jrgo]

Hi @gbruschi,

Happy to help!

Best practice would be to prep each source before hand to handle instances like these and to prep your values for better matches. What's best for your scenario exactly? It's hard to say without more details to WHY two records coming from the same source and look to have matching values are being grouped into separate records. 

That said, I don't think using the unique tool right after the merge would be the way to go because you'd end up losing trans_amounts that you're trying to SUM up. Doing purges beforehand is probably going to be your best bet.

Best,

Jimmy