Joining or Matching non-identical fields

I have two input data documents will over 10,000 lines each of which is generated by a separate program and am trying to join the two by reference numbers. However there are slight differences in the reference numbers between the two systems due to how they are input. For example Program A might have FGRT102 and Program B might have FGRT102$ with the only difference being the $ at the end. To add to the difficulty, some reference numbers are 9 digits while others might be only 5 or up to 12. Is there any way to join these?

Tips and Tricks

Help

Fuzzy Match

Join

Preparation

Accepted answers

All comments

atcodedog05

Hi @MattR79

Find and replace might be of help. It can search in between strings and match. You can also maybe use data cleanse tool to clear special characters.

https://community.alteryx.com/t5/Interactive-Lessons/VLookUps-with-Designer/ta-p/80201

Hope this helps 🙂

apathetichell

you can try fuzzy match - but honestly that's so much better for NL than for ids. Id's vary by 1 digit in 100 and are totally different. words less so.

the best way is to try to figure out the way to edit your primary keys is to have some underlying matching subset of identifiers... Post some sample data and I can explain it - but there has to be a base primary key between some of the two data sets digits.

MattR79

I tried using find and replace and for some reason it replaced all of my data not just the reference IDs I was trying to do.

atcodedog05

@MattR79

Hmm that doesn't happen. Can you provide us a snapshot of the issue.

MichaelSu

Hi @MattR79 ,

It may be tough but you can try the Fuzzy Match tool. Please see attached as an example.

Thanks,

Mike

completed_workflow.yxzp

MattR79

Apologies, if the workflow is messy, I'm very new to this. I am trying to find the Aliases coming out of the union that may not match exactly with the secondary JPM reference numbers from the top input report and replace those so I can then join them. The top input has the clean reference numbers. I need to ensure that all items between the files match as this is going to be use for balance reconciliation between two systems.

Recon WF V4.PNG

apathetichell

Hi @MattR79 - that's just screen grab of your workflow. Can you post sample data??? I'd really need to look at the formats of the two match columns... If there is a reconciliation you can describe, it can be implemented in Alteryx.

atcodedog05

Hi @MattR79

Can you uncheck replace mutiple found.

Can you show us output of the find & replace tool. And sample of alias and reference number.

MattR79

Here is some sample data similar to what I might have between the two programs. The issue that I have is that the aliases vary in length and the amount of trailing characters.

Program A Alias	Program B Alias
TTTS-345623	TTTS-345623$
NUSCGS005431	NUSCGS005431EXT
P-234571A	P-234571
AHKHKI115694	AHKHKI115694ZZ
CPCS-03248010	CPCS-03248
1000054274	1000054274X

apathetichell

would it make sense to try to just match the first 5 or 6 characters from each?

apathetichell

Also if Program A is always included in Program B there are some ways you can do a wildcard non-join match... You can have both in a macro where he Program A is the macro Input and the B is a static input. It can then search if value A+wildcard is is Program B Alias....

MattR79

Unfortunately due to the varying length and the fact that there may be 10 or 15 lines all of which have the same first few digits that is not an option here.

MattR79

Is that something you could show me? I am pretty new to this and would have no idea how to set that up.

MattR79

Is that something that you could show me an example of?

Jean-Balteryx

Hi @MattR79 ,

Can your values have more than 1 trailing characters ?

apathetichell

Post some sample data and an example of the output you would want. You also kind of need an arbitrary number of characters where you deem the ID the same... The way how your sample data is it's impossible for me to know if there is some other entry with the first 7 or 8 or 10 characters which are identical. One datasource has to have a base id- no?

MattR79

See below for the sample data, I need a way to join the two data sets based on the alias, I could assume that the aliases in Program A would be correct so I need to see if there is a way to do that. The issue is that the data below is representative of the string lengths I work with and I cannot trim them down to a set number of characters as some items like the AHKHKI are sequential and trimming down the characters would eliminate those.

Program A Alias	Program B Alias
TTTS-345623	TTTS-345623$
NUSCGS005431	NUSCGS005431EXT
P-234571	P-234571A
AHKHKI115694	AHKHKI115694ZZ
CPCS-032480	CPCS-032480!
1000054274	1000054274X

MattR79

No, generally not. There is typically only one trailing.

Jean-Balteryx

Hi @MattR79 ,

Here is a first solution ! It may be improved but it works !

match_values.yxmd

MattR79

I will give this a shot and see what I come up with. Thank you.

apathetichell

Here's an alternative version... This is the usual id match strategy I use. I haven't seen @Jean-Balteryx 's workflow - but I would expect that his works perfectly for the data you have provided. Obviously with the limited data we can't tell what other variations may occur in the full set.

id matcher.yxzp

ncrlelia

Hi @MattR79

My suggestion is based on the assumption that the main difference is the trailing characters. That is, one alias is always a subset (from the left) of the other.

The main idea is to use the shorter form (regardless in which Program Alias) to be the key value to lookup.

1. a. Find within Program B Alias for values matching Program A Alias, based on the beginning of the field.

And output the Program A Alias - this is Program A Alias2 in the output (screenshot below)

b. Keep records whose Program A Alias2 is not null

c. Use select tool to exclude (original) Program A Alias and Rename Program A Alias2 to Program A Alias.

2. Repeat step 1, using Program B as the reference.

a. Find within Program A Alias for values matching Program B Alias, based on the beginning of the field.

And output the Program B Alias - this is Program B Alias2 in the output

b. Keep records whose Program B Alias2 is not null

c. Use select tool to exclude (original) Program B Alias and Rename Program B Alias2 to Program B Alias.

3. Union the 2 outputs from Step 1 & 2.

Create ID. You can then add the ID to each the 2 datasets, and subsequently join the 2 datasets based on this ID.

Hope this helps.

Cheers,

Lelia

New Workflow4.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703