Skip to main content
Solved

How to check if value in one table exists in another table?

  • March 26, 2018
  • 7 replies
  • 676 views
P
Forum|alt.badge.img

Hi there,

I have a table (lets call it TableA) which contains a field (lets call it Types). I need to compare each value in the Types field with ALL of the values in another field (lets call it OriginalTypes) which is in another table (lets call it TableB). If there is a match a value gets updated, if not nothing happens.

Currently I have these 2 tables feeding AttributeManager which has a Condition Statement checking if Types In OriginalTypes but this is not producing my expected results.

Any thoughts on what I am doing wrong here or what might be a better approach?

Regards

Best answer by erik_jan

I would use the FeatureMerger transformer (joining on Types = OriginalTypes):

Merged output: exist in both

Not Merged output: exists only in A

UnusedSupplier output: exists only in B

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

7 replies

erik_jan
Contributor
Forum|alt.badge.img+22
  • erik_janContributor
  • Contributor
  • 2179 replies
  • Best Answer
  • March 26, 2018

I would use the FeatureMerger transformer (joining on Types = OriginalTypes):

Merged output: exist in both

Not Merged output: exists only in A

UnusedSupplier output: exists only in B

P
Forum|alt.badge.img
  • potterman81
  • Author
  • 8 replies
  • March 26, 2018

I would use the FeatureMerger transformer (joining on Types = OriginalTypes):

Merged output: exist in both

Not Merged output: exists only in A

UnusedSupplier output: exists only in B

I do not want to merge anything, just check to see if the value exists in another table.

 

 

erik_jan
Contributor
Forum|alt.badge.img+22
  • erik_janContributor
  • Contributor
  • 2179 replies
  • March 26, 2018
I do not want to merge anything, just check to see if the value exists in another table.

 

 

But trying to merge the features shows if they exist in one or both datasets.

 

To speed up the process you could even remove all other attributes and duplicate values before the FeatureMerger, using AttributeKeeper and DuplicateRemover transformers.

 

The you end up with a list of types from both (Merged), from only A (Not Merged) and from only B (UnusedSupplier).

 

 

P
Forum|alt.badge.img
  • potterman81
  • Author
  • 8 replies
  • March 26, 2018
But trying to merge the features shows if they exist in one or both datasets.

 

To speed up the process you could even remove all other attributes and duplicate values before the FeatureMerger, using AttributeKeeper and DuplicateRemover transformers.

 

The you end up with a list of types from both (Merged), from only A (Not Merged) and from only B (UnusedSupplier).

 

 

Ok, that makes sense. So how would I go about using that list in the Condition Statement? When I try to use the list it makes me choose a specific value instead of the whole list.

 

 

erik_jan
Contributor
Forum|alt.badge.img+22
  • erik_janContributor
  • Contributor
  • 2179 replies
  • March 26, 2018

I would use the FeatureMerger transformer (joining on Types = OriginalTypes):

Merged output: exist in both

Not Merged output: exists only in A

UnusedSupplier output: exists only in B

I added a screenshot of the workspace:

 

 

D
Forum|alt.badge.img
  • davidrich
  • 48 replies
  • March 28, 2018

Hi @potterman81,

FeatureMerger is the tool you want to use as @erik_jan suggested, see the attached workbench. featuremerger.fmw

FeatureMerger will merge the attributes from TableB to TableA however if the matching attributes are the same and TableB has not other attributes then it will work the same as a conditional statement as no new attributes would be added (See Example 2)

P
Forum|alt.badge.img
  • potterman81
  • Author
  • 8 replies
  • March 28, 2018

Hi @potterman81,

FeatureMerger is the tool you want to use as @erik_jan suggested, see the attached workbench. featuremerger.fmw

FeatureMerger will merge the attributes from TableB to TableA however if the matching attributes are the same and TableB has not other attributes then it will work the same as a conditional statement as no new attributes would be added (See Example 2)

Thanks for the additional explanation, I am still new at FME and am still trying to wrap my head around the workflows but this is starting to make sense. I will mark this as the accepted answer but both yours and @erik_jan are correct. Thanks guys for your assistance with this!

 

 

Community Stats

32,087
Posts
121,853
Replies
39,729
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.