Skip to main content
Question

Change the value of an attribute depending on the value of other attribute


Hello,

I would change the value of an attribute in a table depending on the value of other attributes group of records. Concrete example :

INPUT

NameColorAreaAAAYellow150AZARed56BDEGreen29ABABlue298CDEGray8AZABlack76ABAOrange84BDEPurple162AZAWhite65

 

Sort by "Name" and "Area"

NameColorAreaAAAYellow150ABABlue298ABAOrange84AZABlack76AZAWhite65AZARed56BDEPurple162BDEGreen29CDEGray8

 

and give the same Color value as the record with the highest area

OUTPUT

NameColorAreaAAAYellow150ABABlue298ABABlue84AZABlack76AZABlack65AZABlack56BDEPurple162BDEPurple29CDEGray8

 

Thanks for your help.

5 replies

jdh
Contributor
Forum|alt.badge.img+28
  • Contributor
  • January 7, 2020

One way would be with conditionals and adjacent feature handling.

 

 

If your data is sorted as per the second example, then an attributeCreator to change the value of Color.

 

 


ebygomm
Influencer
Forum|alt.badge.img+39
  • Influencer
  • January 7, 2020

This workflow should do what you want

 

The AttributeRemover and AttributeKeeper aren't strictly necessary as you could set Conflict Resolution to Use Supplier in the FeatureMerger instead


Forum|alt.badge.img+2
  • January 7, 2020
jdh wrote:

One way would be with conditionals and adjacent feature handling.

 

 

If your data is sorted as per the second example, then an attributeCreator to change the value of Color.

 

 

@pedro I've attached the example workspace to illustrate @jdh 's solution (2019.1): adjacentattributes.fmwt

More on the Adjacent Feature Handling in the AttributeCreator documentation under the section Advanced Example: Using Multiple Feature Attribute Support


bwn
Evangelist
Forum|alt.badge.img+26
  • Evangelist
  • January 8, 2020
ebygomm wrote:

This workflow should do what you want

 

The AttributeRemover and AttributeKeeper aren't strictly necessary as you could set Conflict Resolution to Use Supplier in the FeatureMerger instead

I would similarly use essentially the same solution shown by @ebygomm. The key is DuplicateFilter, what it does is take the "first" feature within a Group and sends that to the Unique Port, and any others within that group gets sent to the Duplicate Port.

If you send a Sorted table, then DuplicateFilter will automatically then take the Top/First result in each Name group, and then the Top/First Color can be joined back into the original table with a FeatureJoiner or FeatureMerger (FeatureJoiner tends to work faster for large datasets).


bwn
Evangelist
Forum|alt.badge.img+26
  • Evangelist
  • January 8, 2020

Alternatively InlineQuerier can do it in one step. If you send the unsorted, raw table to InlineQuerier and say name the table "ColorTable" then using this as the SQL Expression for the Output within the Transformer will work:

SELECT A.Name,
(SELECT Color FROM ColorTable WHERE Name=A.Name ORDER BY Area Desc LIMIT 1AS MaxColor,
A.Area
FROM ColorTable A

Essentially it makes an Attribute "MaxColor" as a value derived from Subquery that sorts by Area in Descending order within its Name Group, and takes the top row as the Color result (this is what "LIMIT" does)

Input Table

0684Q00000ArJKLQA3.jpg

Result Table

0684Q00000ArJKCQA3.jpg


Reply


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