Skip to main content
  • EN

Dear Sir / Madam,

 

Actually I am trying to match two excel table values (attributes name from two different samples).

 

In the excel, I have one matching table and two models attributes in two tab sheet.

 

I would like to compare the value with Model 1 and Model 2 based on the matching table. For example, apple matching apple. However, some of the naming are not exactly matched with wording and are slightly different, such as Pineapple in sample 1 is equal to USA_Pineapple in sample 2. Is it possible to do that? Also, if possible, could it be not case sensitive?

 

Then, I would like to perform a value check on two samples based on the matching table I provided. The result that I want to get is on the "Output Sample" tab in orange colour.

 

I have attached my sample data here. I hope you understand what I said and I hope it is possible to do that.

 

Thank you all and have a good day!

You are probably looking for the FuzzyStringComparer.

You are probably looking for the FuzzyStringComparer.

Added a workspace demonstrating this.

Added a workspace demonstrating this.

Oh my god! thank you so much for your detailed illustration. It is more complex than what I expected. I will take a look how it work first.

 

Thank you soooooooooooooooooooooo much for your help. I am really appreciated it. :)

Added a workspace demonstrating this.

Hi @nielsgerrits​ , may I ask what is the meaning of Requestor = 1 and Supplier = 1 in the first FeatureMerger transformer?

 

Also, it seems that you are checking the ratio with "MatchRatio"?

If I change the matching table (or add more attributes in it), is the MatchRatio = 0.8 still valid for all cases?

 

Thank you very much.

 

Added a workspace demonstrating this.

The goal is to find the best fitting supplier for each requestor. To make these comparisons, you will need all combinations of the two tables. When using the FeatureMerger with a value for suppliers that equals the value for requestors, all suppliers will be merged to all requestors. It will put the suppliers in a list, this is why you need a ListExploder to get back all the features.

 

The FuzzyStringComparer is outputting a match ratio. You need judge if a comparison was "successful". After some runs I found that a value of 0.8 returned the results you were after in the sample you provided.

Reply


Community Stats

30,129
Posts
114,378
Replies
37,768
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.