topic wildmatch data from two tables without linking those tables in Connectivity & Data Prep

wildmatch data from two tables without linking those tables

MahendranCS — Thu, 20 Mar 2025 04:33:27 GMT

I have two tables: Master and Transaction.

Master has one column named Source, which contains string values, and the name column in the Transaction is made up of string values.

I need to create two new columns, Is Available and Split, based on the columns mentioned earlier. If the value in the Name column fuzzy matches with a value in the Source column, the new column Is_Available should populate Y; otherwise, N. Similarly, if the Source column fuzzy matches the Name column value, the Split column should populate the Source value in it; otherwise, nothing. It has to work like fuzzy lookup.

For example, in the attached picture, the df value in the Name column is present in the Sdf and Aspdsdf values in the Source column, and hence Y is populated along with the Source values in the new columns.

Re: wildmatch data from two tables without linking those tables

henrikalmen — Thu, 20 Mar 2025 08:28:26 GMT

Perhaps you can make it work using the function LevenshteinDist().

Or experiment with mixmatch().

Re: wildmatch data from two tables without linking those tables

marcus_sommer — Mon, 24 Mar 2025 13:02:27 GMT

You will probably need multiple steps to get your wanted matching. The first one may be to create a mapping table from the transactions to transfer these information into the master-table, for example:

first: mapping load distinct
Name,
'<<' & Name & '|' & recno() & '>>'
resident Transactions order by NameLength, Name;

second: load Source, mapsubstring('m', Source) as Match
from Master;

Afterwards you could count the delimiter within Match to detect the number of matchings and using textbetween() and subfield() to extract the match-parts and their sources and/or applying n further logic.