Solved

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

  • 26 March 2018
  • 7 replies
  • 89 views

Badge

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

icon

Best answer by erik_jan 26 March 2018, 19:40

View original

7 replies

Userlevel 2
Badge +12

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

Badge

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.

 

 

Userlevel 2
Badge +12
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).

 

 

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

 

 

Userlevel 2
Badge +12

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:

 

 

Badge

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)

Badge

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