Finding different length values into 2 data sources

[uak]

Hello Experts,
I have 2 documents containing data of different length which i wanted to match/compare in order to find out the matching key value:

Goal
1. Mainly i wanted to look my Doc 1 Column into the Doc2 and wanted to find the Mode & SW values.
2. How many of them match and also the non-matching one(s)
Doc 1 (csv file) : Contains one column . The key field has around 11 or more character length
like as below

Key

M31AA210000

M31AA210011

M31AA210999

M32BB112222

Doc 2 (csv file): Contains more than 1 column. But the Key field have different length

this is how Doc 2 look like

Key	Date	Mode	SW
KEERMM31AA210000	19.05.2020	Open	20.01
KRRRMM31AA210011	19.05.2020	Close	20.02
KPPRMM31AA210999	19.05.2020	re-open	20.03
KPPRMM32BB112222	19.05.2020	Adjusted	20.03
KPPRMM32BB112222RR	19.05.2020	re-open	20.03

I have used the Filter Tool but to put a lot of values one-by-one into filter tool doesn't look to be an appropriate way.
Can you suggest me any smarter idea. May any tool can compare Key of different length ?

Thanks
Regards

[ggruccio · Accepted solution]

Hi @uak,

Since the Key in file 1 can be 11 or more character length it won't be as simple as just matching on the Right 11 characters from the Key in file 2. 

Just use the Append Fields tool to create a matrix with every possible combination of matches and then use some formula and filter logic that looks for the Key in File 2 to contain the Key in File 1.  Sample workflow attached.

[uak]

Thanks  ggruccio

What i have noticed is that Append Tool is handling only 18 records .

Although i have 4000 records in file1 which i wanted to look in file2 which has 10000 records.
"Error: Append Fields (40): There were more than 16 records in the source." This is a sign that for such a large data Append is not the best option to use?

I am now using Simple formula tool