Matching two strings and copying data

Help
1

A quick overview:

I am creating two data tables from sets of company data - Invoice Data and FAME Data

For the Invoice Data i am updating it so that for any records that contain Ltd or Limited i am removing the end of the text. e.g. ‘Kenstone Properties Limited’ becomes just ‘Kenstone Properties’.

I am then trying to match the Invoice Data with the FAME Data. The FAME data may contain e.g. ‘Kenstone Properties Limited’ so i want to see if the Invoice data “Contains” the same.

Issues i am encountering:
(i) I am unable to return a positive match. Though there should be at least one match between ‘Kenstone Properties Limited’ in the Invoice Data to ‘Kenstone Properties Ltd’ in the FAME Data.

(ii) If a match is found i want to enter into the Invoice Data Table a “Yes” in the ‘Match’ column i have added. If there is no match then a “No” is entered.

(iii) If a match is found as “Yes” then it should extract from the FAME Data and enter it into the Invoice Data. I have setup the variables for the FAME Data e.g. stFAMERegisteredNumber etc

FAME Validation.xaml (31.4 KB)

Data Table 1
Invoice Data.xlsx (678.2 KB)

Data Table 2
CosOver5Employees_UKEir.xlsx (30.0 KB)

2

Hi @jon1302

The logic you have used here seems to be fine… However the values you are getting seems to be bit off.

image

See the values of two variables used here

image
image885×373 26.2 KB

image
image891×508 33.3 KB

It doesn’t look right to me… Is this what you should get here?

1 Like
3

These look like the correct values. In the FAME data table (dtCos) there should be a value for Ashford Cattle Market Company for it to pick up. I’ve tried an alternative route by updating both data sets and then just using a join data table.

4

@jon1302 Try Like below

image
image1920×1080 191 KB

5

Try using Filter Data table Activity.Use for each and fetch the column data in invoice table save the data in a variable and pass the variable data as input parameter for Value in filter data table and Input relevant column name in FAME data table you want to compare