Question

Updating filed values from another table based on multiple conditions

  • 12 December 2018
  • 4 replies
  • 26 views

Hi,

I am a fairly new FME user and I don't have a lot of experience with it so I was hoping somebody could help me out.

I have two shapefiles and I want to update field values from one table with values from another. The "Line table" had fields From and To Node ID that matches with the "Nodes table" Node ID.

I have sorted out all the nodes that have no Z value and I want to update that filed with values from the "Line table" with the condition that the lower value is chosen if From and To Node_ID_Z are different. So for example for Node ID = 3 I would like to have a value 1,8.

But I don't know how to properly join these two tables and set this condition. Any suggestions?


4 replies

Userlevel 4
Badge +25
Duplicate the data to 2 AttributeManagers, use one to create a new field (Z) from From_Node ID_Z and a new field (Node_ID from the From_Node ID. Then the second one to do the same but using the To fields. So you now have 12 features in that set.

 

Join it to the nodes, using the Node ID. LEt it generate a list, sort that, ascending, on the Z and use a ListIndexer to copy the Z value.

Badge +2

Hi @iva,

Alternatively you could use a combination of FeatureJoiners and AttributeManagers to achieve this, I've included the example workspace below.

xlsxr2none.fmwt

Badge +22

You could also do it with a single inlineQuerier, but I don't know that it would be more efficient than @redgeographics solution.

 

My brute force sql statement is below, but I expect someone who knows more about it could vastly improve upon it.

 

 

SELECT "fme_feature_content", A.Z
FROM "Nodes"
LEFT JOIN
(SELECT ID, Â MIN (x) as Z
  FROM 
 (SELECT "From_Node ID" AS ID, "From_Node ID_Z" as x
  FROM "Lines"
  UNION
  SELECT "To_Node ID" AS ID, "To_Node ID_Z" as x
  FROM "Lines")
 GROUP BY ID) A
WHERE "Nodes"."Node ID" = A.ID

The benefit is you could have multiple lines with the same From/To Node Ids, and it would still take the minimum Z.

Hi @iva,

Alternatively you could use a combination of FeatureJoiners and AttributeManagers to achieve this, I've included the example workspace below.

xlsxr2none.fmwt

Thanks a lot, @hollyatsafe, I used your template and just changed that the first FeautreJoiner "Uses Right" for Attribute Conflict Resolution and it worked great!

Reply