Fuzzy Match Tool gives different matches depending on sorting

Question

Hi Alteryx Community,

I have a problem with my Fuzzy Match Tool. I'm matching vendor names from one dataset (checking potential duplicates) but the results are different depending on the sorting I add before the tool. I understand that the order would be different but I assume the number of matches should always be the same.

Am I doing something wrong or does the tool have some limitations I'm not aware of?

I have around 50k records and the Tool is finding 8k (no sorting), 22k (ascending) or 10k (descending) matches.

My setup below:

SandraSkrzynska · Answer

1,461,881 records is news to me... but that would mean it's only my problem and not the Tool.

My Unique Tool results are:

Ascending 891,179

Descending 861,513

All 1,311,172

As for matches with record 1 - even when I add Browse Tool I see only 27 matches and nothing over 40k is matching.

I assume it's my computer not having enough memory, but Alteryx is not giving me any errors or even a message that it's struggling. I remember that on some other really heavy reports I got sometimes messages "The Designer x64 reported: Beginning to compact waiting packets to reduce memory usage".

I will try designing a workaround so datasets are divided to not exceed 40k records. Hopefully this will help.

ChrisTX · Answer

Where are you seeing different record counts?  You have 3 Unique tools in your workflow.  Each of the 3 U output anchors has a record count of 1,461,881.

The workflow ran for me in 4 minutes 55 seconds, using the AMP engine.  I have 64GB ram on my machine.

You mentioned "I do not see record 1 matched with anything over record 39001".  If you use any output anchor, you may be seeing on partial output. The results window will display text like "22,458 of 1,461,881 records displayed (partial results).

After the U anchor on each Unique tool, add a Browse tool to see all of the results.

Each output anchor on each tool has a "Browse anywhere" feature, but you may be seeing only partial output depending on the size of your datasets and your machine memory.  And you'll see the text "(partial results)" in the results window. An actual Browse tool will always show all of the records.

Chris

SandraSkrzynska · Answer

@ChrisTX wrote:If you add a Unique tool immediately after the Fuzzy Match tool, and ensure the two record IDs are unique on one row, do you still get different record counts?

Are you able to post a sample workflow with a small sample dataset, even if you have to modify the data to avoid sharing sensitive information?

Chris

I managed to recreate the situation on sample database. Tool is working fine on smaller datasets but when the file is bigger it looks like it's not checking all records and stop at some point. The sample database I created is a multiplication of 3k records so you should see a repetition in matched IDs and every record should have at least several matches. But I do not see record 1 matched with anything over record 39001. That would explain the difference when I add sorting.

Deduplication-error.yxzp