Skip to main content
  • EN

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

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.

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.

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


Community Stats

30,108
Posts
114,295
Replies
37,744
Members
Terms & ConditionsCookie 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.