Using multiple key columns for Join/Lookup/Intersect/Subtract

[Admin]

You can Join, Lookup, Intersect or Subtract on more than one key column by creating a composite or compound key column. using Concat Cols.

For example, if want to Join this table using both Catalog and Part as key columns:

To this table to this table using both SupplierCatalog and PartId as key columns:

So that rows are joined only when catalog and part both match, then you need to create a new composite/compound key column for each dataset using Concat Cols:

Use a delimiter in Concat Cols, otherwise concatenating AB with CD will produce the same key as concatenating A with BCD. Use a delimiter that is unlikely to occur in either key.

You may need to use Copy Cols or Rename Cols to get the key columns in the same order in each dataset, before Concat Cols.

[Olaf]

And by the way, in case the Key is part of string with different characters around, but on a fixed position in the string, the column can be copied and in the copied column the key can be set standalone using EXTRACT transformation.
If it isn‘t a fixed position it can be checked if with SPLIT COL the key can be extracted and used.