Skip to main content
Solved

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

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

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

7 replies

erik_jan
Contributor
Forum|alt.badge.img+17
  • erik_janContributor
  • Contributor
  • 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
erik_jan wrote:

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+17
  • erik_janContributor
  • Contributor
  • March 26, 2018
potterman81 wrote:
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
erik_jan wrote:
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+17
  • erik_janContributor
  • Contributor
  • March 26, 2018
erik_jan wrote:

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
  • 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
davidrich wrote:

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!

 

 

Reply


Helpful Members This Week

Community Stats

30,937
Posts
117,250
Replies
38,740
Members

Latest FME

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.