Skip to main content

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.

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.

 

 


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


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


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).


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 1) AS 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