Question

Matching two tables and output result

  • 18 November 2022
  • 5 replies
  • 63 views
Z
Badge

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!

1 Attachment

5 replies

nielsgerrits
Rank
Userlevel 6
Badge +32

You are probably looking for the FuzzyStringComparer.

nielsgerrits
Rank
Userlevel 6
Badge +32

You are probably looking for the FuzzyStringComparer.

Added a workspace demonstrating this.

Z
Badge

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. :)

Z
Badge

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.

 

nielsgerrits
Rank
Userlevel 6
Badge +32

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

28,661
Posts
109,324
Replies
36,528
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.