[GIS] Update for field values from another table field values with condition

Question

I have two tables in file geodatabase and both are the same structure.

Both tables are having around 140 columns and 4000 rows.

In table 1 there are many fields are "null" and "0" and remaining fields are filled with some values.

In table 2 filled with some values and also few fields with "null" and "0" values

In both table is having "ID" common unique value field available.

Condition is.

I need to update Table 1 based on below scenarios

1. If fields values available in both then no need update

2. If table 1 is having "null" and "0" values then update new values if available in table 2.

3. If table 1 is having some value and if table 2 is having "null" and "0" then no need to update

Answer 1

I have two ways to do this.

One is to rename the attributes in both tables to unique names, merge the records with the same ID into one record and create new, original, fieldnames. This way you are fully in control and you are able to handle each field its own way. Performance will be quite good as this way of working is not hard on internal memory. Downside is a lot of fields means a lot of work.

• Read both tables.
• Prefix attribute names with BulkAttributeRenamers. For example "Table1.FieldA" and "Table2.FieldA".
• Merge records from tables with FeatureMerger based on ID.
• Use AttributeCreator with conditional value to recreate fields based on conditional per field. For example "FieldA" : If "Table1.FieldA" = "Table2.FieldA" then "Table1.FieldA", Else If "Table1.FieldA" is null or "Table1.FieldA" = 0 then "Table2.FieldA", Else If "Table2.FieldA" is null or "Table2.FieldA" = 0 then "Table1.FieldA", Else "DUMMY" (I think you can create one and duplicate if you want. If not you could use an AttributeCreator for each field to prevent doing double work.)
• Use Tester to check if values are DUMMY.
• If fields are correct use Keeper to remove the "Table1." and "Table2." fields.
• Write output.

The other, more generic, solution is to create a feature for each field, merge the field features with the same ID and fieldname into one record and create new, definitive, fieldnames. Then aggregate the field features back into the original records. This way you only have to use one AttributeCreator for each variant. A bit harder to design, harder on performance as you will get rows x columns number of features. But you wont have to rebuild each different fieldname. Which is a big plus with this much attributes :)

Tip: If the tables have geometry, the GeometryExtractor and GeometryReplacer are a nice way to control geometry flow.

FME 2017.1 workbench download