Skip to main content
Question

Updating filed values from another table based on multiple conditions

  • December 12, 2018
  • 4 replies
  • 247 views
I

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

    redgeographics
    Celebrity
    Forum|alt.badge.img+48
    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.

    H
    Forum|alt.badge.img+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

    jdh
    Contributor
    Forum|alt.badge.img+28
    • jdhContributor
    • Contributor
    • December 12, 2018

    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.

    I
    • iva
    • Author
    • December 13, 2018
    hollyatsafe wrote:

    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


    Helpful Members This Week

    Community Stats

    30,957
    Posts
    117,330
    Replies
    38,745
    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.