Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Fuzzy Dates

PeterAP
8 - Asteroid

Hi All,

 

What's the best way to do a fuzzy match on dates plus a description category?

 

I'm looking to see if there have been errors in the data entry process. For example the actual data that shoould have been input is Date: 06/05/2020, Description: Fell off Ladder. So i'm looking to identify cases such as where the following might have happened:

 

1. Date: 05/06/2020 Description: Fell off Ladder

2. Date: 06/06/2020 Description: Fell off Ladder

3. Date: 06/05/2002 Description: Fell off Ladder

 

I have been able to find fuzzy matchs if the "Fell off Ladder" part was slightly different, but am struggling to find the best way for dates.

 

Can anybody help?

 

Thanks,

 

Pete

10 REPLIES 10
patrick_mcauliffe
14 - Magnetar
14 - Magnetar

Hey Pete.

Can you post an example of what you've tried so far?  There are probably more than one way to do this but it depends on the data structure (are these in separate fields?  what are the field types? date format? etc).

PeterAP
8 - Asteroid

Hi Patrick,

 

Thanks for the quick reply.

 

I've posted some mock up data here in an Alteryx Package.

 

The data is in separate fields - and I've indicated which ones I would expect to come up as fuzzy matches. A fuzzy match may return more results which is fine - but at the moment, it is only matching when the date is exact.

 

 

patrick_mcauliffe
14 - Magnetar
14 - Magnetar

It appears the Excel sheet didn't attach in your package.

When you export the package make sure that the box next to the sheet on the assets list is checked.

PeterAP
8 - Asteroid

Apologies - hopefully you can now find the file in the attached

grazitti_sapna
17 - Castor

Hi @PeterAP , fuzzy matching is best applied to String field types but you can try this .Attaching the file along for your reference.

Sapna Gupta
patrick_mcauliffe
14 - Magnetar
14 - Magnetar

@PeterAP ,

Like @grazitti_sapna  mentioned, the fuzzy match is meant for a string.

With that in mind, we can manipulate this data to function like a string.

First, add a Select tool and change the field type to a string.

Next, to get the fuzzy tool to generate a key in the right order we need to change the date format to mm-dd-yyyy.

From there, it's a matter of tweaking the fuzzy match settings as if it were just a plain string.

In my attached example, I stripped the "-" from the date and set the max key length to 6.
Then, use the matching functions for only characters and make sure that your keys use digits.

Note that I severely lowered the matching threshold just to allow for a wider range to demonstrate on.

 

Also, I assumed you wanted to make sure there was a match on the description too, so I used a string match on that too.

You can just delete that match setting if I guessed wrong.

 

I'm not quite sure how the output would be used, so I just had the matched keys filter out to only show those that had a match regardless of the match it had.

 

PeterAP
8 - Asteroid

Thank you both for your suggestions!

 

@patrick_mcauliffe - Unfortunately I don't seem to be able to open the workflow - the error message I get is that it was created on a newer version of Alteryx although when I check for updates - I'm on the current version!

 

@grazitti_sapna- I think the file you attached may have been the original version I sent - do you have a copy of it somewhere you could upload again?

 

grazitti_sapna
17 - Castor

Hi @PeterAP ,  I have attached the file.

Sapna Gupta
patrick_mcauliffe
14 - Magnetar
14 - Magnetar

Hey @PeterAP, what's the version number you have?

Also, you can just change the workflow version from Notepad to force it into compatibility with whatever version you have.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-open-newer-version-yxzp-files/t...

 

 

Labels