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
Solved! Go to 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.
Thanks ggruccio