Question

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

  • 7 January 2020
  • 5 replies
  • 6 views
P

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
Rank
Badge +22

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
Rank
Userlevel 1
Badge +10

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

M
Badge +2

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

B
Badge +3

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

B
Badge +3

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


Community Stats

28,670
Posts
109,364
Replies
36,531
Members
Terms & ConditionsCookie 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.