Matching new (but pattern-ized) ID's to correspond to one out of a couple Lookup values

[carlstrum]

Hey Everyone!

I am looking to automate a process for classifying new undefined (yet patternized) Product ID's based on a previous set of data with similar fields already classified.  

I use a Lookup on a file that has a large list of all of these unique Product ID's with classified Product Names, but it is static, and the new Product ID's come in from a separate database.

Here's a visual example of what I'm trying to do: (looking to solve for the "???" fields)

Product ID	Product Name
4544123	A
4544124	A
12322	B
12323	B
789123	C
789124	C
4544125	???
12324	???
789125	???

If you eyeball this chart, (and are a fan of hard assumptions) you could tell that the last 3 unclassified Product ID's are A, B, and C respectively based on the shared patterns it has with existing classified information, (just looking at the pattern of the first couple digits in each value). I am currently adding these manually back into the larger list lookup, which isn't optimal for an analyst :) .

I am believing that a fuzzy match would be the way to solve this, but I'm having no luck using it for what I want to solve.

Anyone solve a problem like this before with Alteryx?

Thanks!

[Joe_Mako]

My understanding of the fuzzy match tool is that digits are expected to be always be an exact match. I currently do not know of a way to use the fuzzy match tool to find digits that are near each other.

Alternatively, you can create whatever logic you want in Alteryx, so I based on your description, I made the attached.

It takes in two inputs, one existing known Product ID and Name combinations, and another that is a list of new Product IDs. The first three steps are the same on each, so if your data starts as a single table, you could use a filter tool before the Join to create two streams.

- Select to force to a string data type, may not be necessary if your field is already a string.
- Generate Rows to create a record for each character in Product ID
- Formula to pull out one character per record Substring([Product ID],[Position],1)
- Join on Character position and Value
- Summarize on Product ID, Product Name, and New Product ID, counting the records that matched both position and value
- Formula to calculate a % Matched [Count]/Length([Product ID])
- Summarize to New Product ID and Product Name
- Sort by New Product ID and then % Matched descending
- Sample to pull out first record per New Product ID Group

You can then Union this with your previously known combinations, add a threshold for % Matched with a Filter, or Join with your Input or new records to see if there were some with no characters matching.

This may be too simple so you may need to make the logic more sophisticated to be in line with your Product ID naming conventions. For example you could add logic to ensure the first 3 characters are an exact match.

[SeanAdams · Accepted solution]

Hey @carlstrum

@Joe_Mako is right, the Fuzzy tool generally doesn't digest pure digits well (and do fuzzy matches on them) but there is a way to work around this.

- Append some text onto the product code

- Then fuzzy match, but using the "Zip Code" or a custom match rule that works the same as the zip-code match

Using this - I'm getting a good fuzzy match on your data set - see attached.

Depending on your exact data, you're probably going to have to work with the custom match settings (you may have to trawl the help text, and learn more about Jaro distance than you ever thought you needed :-))  and play around with this a bit, but hopefully this gets you to a solution.   If all else fails, the algorithms underneath fuzzy matching are fairly broadly available, and if it comes to that, then we can look for additional options.

Cheers

Sean

[SeanAdams]

Hey @carlstrum

Just checking in on this thread - did you manage to get to a solution (and if so, would you mind marking this thread as solved)?

If not - let's iterate on this thread to get you to a solution!

cheers - have a good weekend!

Sean

[carlstrum]

@SeanAdams @Joe_Mako

Thanks for the follow up Sean, and thanks to both of you for taking a stab at this!
(I've been told this community is great, but this really goes to show. Really appreciate you guys taking the time to make these sample workflows.

Both of these are pretty interesting - going through them, I definitely realize there's more than one way to skin a cat here.
Tomorrow, I'm going to give these an extra shot, but I also have another idea where I take all of the new and old product ID's, and send them through a series of steps where I'm going to take a series of LEFTS of all product ID's, starting from LEFT(10), deduping, seeing if there are similar fields, and if there are remaining unmatched records, go to LEFT(10-x), where x is 1 and increases each time this process happens.  This probably isn't the most clean way to do something like this, but I'm going to try all three of these to see what works best.

Thanks fellas!!

[SeanAdams]

:-) no worries - let us know if we can help as you go through the next cycle!

Cheers Carl

Sean

[SeanAdams]

Hey @carlstrum

Did you manage to get to a solution on this one?    If so, would you mind marking this as solved (using the green Solution button) so that this can be added to the knowledge base?

If you're still having challenges, feel free to reply, and we can work with you to close this out.

Cheers

Sean

[carlstrum]

Hey Sean - I'll close this one out for now.  I'm still working out by own way but these solutions were definitely super helpful!

[NicoleJohnson]

Hey, I'm a bit late to this party, but since I'm not sure what you're still trying to sort out on this one, figured I'd share what I played around with last night when I saw this post resurrected.

See attached workflow that I think might accomplish what you're looking for? Makes a few assumptions:

1. New ID's are always assigned sequentially.

2. Once a Product is assigned, the New ID & Product will be added to the Lookup list.

3. ID's are numeric only (it's totally possible with letters, but would need to do some trickery with the "+ 1" formulas...)

Anyway, take a look. Maybe there's a nugget in there that might help you out. :)

Cheers! NJ