Skip to main content
Question

FeatureJoiner rejecting where null left key

  • November 19, 2019
  • 3 replies
  • 449 views
markcoopersdc
Contributor
Forum|alt.badge.img+6

I am joining 3 tables together. There will always be a record in the first table and there may or may not be a related record in the second. If there is a related record, there may or may not be a record in the third. I always want to have the record from the first table.

The relationships are as follows:

Left: Table 1 primary key - Right: Table 2 foreign key

Left: Table 2 primary key - Right: Table 3 foregin key

When I try and set this up using FeatureJoiner transformers, it works ok on the first, but if there is no record in the second table, it cannot join to the third as there is no left key.

Is there any way to pass records with a null left key in FeatureJoiner? If I did a similar workflow in a Microsoft Access query I would still have the record from the first table. I presume I could use a FeatureMerger and pass Merged and UnmergedRequestor into the input of the next transformer. This could work ok but I do much prefer the FeatureJoiner

Many thanks

3 replies

redgeographics
Celebrity
Forum|alt.badge.img+49
If a feature from Table 1 fails the first FeatureJoiner it should come out the UnjoinedLeft port, so you can route it to the output accordingly.

 

Hope this helps.

mark2atsafe
Safer
Forum|alt.badge.img+44
  • mark2atsafeSafer
  • Safer
  • November 19, 2019

You could also use a Tester before the FeatureJoiner to test for Attribute Is Null (or even Attribute Has a Value) and keep them separate.

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Contributor
  • January 23, 2024

I dealt with this issue by using AttributeCopier to make a copy of the attribute that contained NULL for some features (your left key attribute). Then I used NullAttributeMapper to replace NULL with a custom value (e.g. |||NULL|||) in the copy attribute.

 

In my case, I am running the data through a Postcode matching process. Since the source data might have the same Postcode duplicated many times for different features, I send only unique Postcodes for matching. After matching, I join the results back to the original data.

 

The unique values for matching are unique Postcodes plus the custom |||NULL||| value coming through the Unique port of the DuplicateFilter. The actual matching uses the original Postcode attribute, so if that has a value of NULL, then it won't get a match and a note saying that will be added to the results.

 

FeatureJoiner then joins the matching results back to the original data on the copy of the Postcode attribute (i.e. the one that contains the custom |||NULL||| text for NULL Postcodes). So every feature with a NULL Postcode will get the note saying it was not matched. Then I remove the copy attribute before the results are written.

Reply


Helpful Members This Week

Community Stats

31,328
Posts
118,859
Replies
38,955
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.