Skip to main content
Solved

Combining two tables without a common field

  • February 16, 2024
  • 3 replies
  • 132 views
jeroen_hack
Contributor
Forum|alt.badge.img+7

Hello FME community,

 

I have an issue I'm working on and can't seem to figure out the solution.

 I have two Excel tables. One with different kinds of certifications and the score each is given.

Certification Score
Certification_A 1
Certification_B 5
Certification_C 7

The second one contains Applicants with fields defining the various certifications and links to said certification. One applicant can have multiple certifications. We need to determine for each applicant which is the highest scoring certification.

Applicant_ID Certification_A Cerification_B Certification_C
1   URL to file  
2 URL to file    
3   URL to file URL to file

 

In the second table I want to add the scores and then determine the highest score for each row (this i can do in a new field with the max(Value1, Value2 etc) function) and write out the applicant_id, the highest scoring certification name, it's URL and the score.

Can anyone give me some pointers how i can solve this case?

Thanks for any help,

Jeroen

Best answer by joepk

Hi Jeroen, happy friday! I feel like the AttributeValueMapper could be the solution to your question, but this requires some hard coding of values. A dynamic way of solving this, is using the AttributeExploder:

This is my starting data: 

First you explode your Applicant table using the AttributeExploder (Keep Attributes = Yes), then you use the FeatureMerger to merge the Score values to your attributes (Requestor = _attr_name, Supplier = Certification). You now have the Score field added to your exploded Applicant table:

 

Using a Tester we filter out the features where the Certification Type mentioned in _attr_name  is not equal to “url”, In your case you might have to resort to using “Attribute Has A Value”. You have to make sure you refer to @Value(@Value(_attr_name)), as you want the value of the certification type mentioned in Certification:

This leaves us with 4 features for 3 applicants, one feature for every certification every applicant has.

Using an AttributeCreator we can map our Score value in our Certification_X columns:

Using an Aggregator we recreate our old Applicant table, making sure to Group on Applicant_ID and create a list of scores:

Using a ListSorter, we sort our list Descending. If an applicant has multiple scores, the highest one will be the first list entry. We then use a final AttributeCreator to create an attribute Highest_score:

And there we have the highest score per Applicant_ID.

I hope this helps. I also cannot wait for some Evangelists to recreate my outcome using only 2,5 transformers ;-) 

Have a great weekend!

View original
Did this help you find an answer to your question?

3 replies

joepk
Influencer
Forum|alt.badge.img+20
  • joepkInfluencer
  • Influencer
  • Best Answer
  • February 16, 2024

Hi Jeroen, happy friday! I feel like the AttributeValueMapper could be the solution to your question, but this requires some hard coding of values. A dynamic way of solving this, is using the AttributeExploder:

This is my starting data: 

First you explode your Applicant table using the AttributeExploder (Keep Attributes = Yes), then you use the FeatureMerger to merge the Score values to your attributes (Requestor = _attr_name, Supplier = Certification). You now have the Score field added to your exploded Applicant table:

 

Using a Tester we filter out the features where the Certification Type mentioned in _attr_name  is not equal to “url”, In your case you might have to resort to using “Attribute Has A Value”. You have to make sure you refer to @Value(@Value(_attr_name)), as you want the value of the certification type mentioned in Certification:

This leaves us with 4 features for 3 applicants, one feature for every certification every applicant has.

Using an AttributeCreator we can map our Score value in our Certification_X columns:

Using an Aggregator we recreate our old Applicant table, making sure to Group on Applicant_ID and create a list of scores:

Using a ListSorter, we sort our list Descending. If an applicant has multiple scores, the highest one will be the first list entry. We then use a final AttributeCreator to create an attribute Highest_score:

And there we have the highest score per Applicant_ID.

I hope this helps. I also cannot wait for some Evangelists to recreate my outcome using only 2,5 transformers ;-) 

Have a great weekend!

danilo_fme
jeroen_hack
2 people upvoted this
  • danilo_fme
    danilo_fme
  • jeroen_hack
    jeroen_hack
danilo_fme
Evangelist
Forum|alt.badge.img+41
  • danilo_fmeEvangelist
  • Evangelist
  • February 17, 2024
joepk wrote:

Hi Jeroen, happy friday! I feel like the AttributeValueMapper could be the solution to your question, but this requires some hard coding of values. A dynamic way of solving this, is using the AttributeExploder:

This is my starting data: 

First you explode your Applicant table using the AttributeExploder (Keep Attributes = Yes), then you use the FeatureMerger to merge the Score values to your attributes (Requestor = _attr_name, Supplier = Certification). You now have the Score field added to your exploded Applicant table:

 

Using a Tester we filter out the features where the Certification Type mentioned in _attr_name  is not equal to “url”, In your case you might have to resort to using “Attribute Has A Value”. You have to make sure you refer to @Value(@Value(_attr_name)), as you want the value of the certification type mentioned in Certification:

This leaves us with 4 features for 3 applicants, one feature for every certification every applicant has.

Using an AttributeCreator we can map our Score value in our Certification_X columns:

Using an Aggregator we recreate our old Applicant table, making sure to Group on Applicant_ID and create a list of scores:

Using a ListSorter, we sort our list Descending. If an applicant has multiple scores, the highest one will be the first list entry. We then use a final AttributeCreator to create an attribute Highest_score:

And there we have the highest score per Applicant_ID.

I hope this helps. I also cannot wait for some Evangelists to recreate my outcome using only 2,5 transformers ;-) 

Have a great weekend!

Great solution.

danilo_fme
1 person upvoted this
  • danilo_fme
    danilo_fme
jeroen_hack
Contributor
Forum|alt.badge.img+7
  • jeroen_hackContributor
  • Author
  • Contributor
  • February 22, 2024

@joepk Sorry for the late reply.

Thank you very much for the detailed answer. This was the solution for me!

danilo_fme
1 person upvoted this
  • danilo_fme
    danilo_fme

Reply


Most helpful members this week

Community Stats

30,845
Posts
116,935
Replies
38,669
Members
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.