Create attributes from unique values of another features classes category attribute with FME

Question

I have a table (say landuse polygons for example) structured as follows:

ID | landuse_category
---------------------
1  | woodland
2  | industry
3  | farmland
4  | woodland
5  | lake
6  | farmland
7  | industry
8  | lake

Now I would like to create attributes from the distinct values (farmland,industry,lake,woodland) of landuse_category to a table districts for example like

ID | farmland | industry | lake | woodland

I succeeded in obtaining the distinct values with a DuplicateFilter with key attribute landuse_category on the Unique port and then create a list of these unique values with a ListBuilder like

Since input data may vary, this is a generic task and thus the attribute values themselves should not be part of the process.

How to create attributes on another table using this list?

Answer 1

The simplest way to create the attribute is to use an AttributeManager and use the value of the landuse_category attribute as the name of the new attribute:

When you write that (and I'm using plain CSV format here) the results look like this:

I'm guessing this is what you're looking for. There are two things to consider though...

1) If you want a 0 (or null) value for the other fields, then you could expose those attributes (AttributeExposer) and map them to null (NullAttributeMapper).

2) I'm assuming the output schema is fixed (ie you know the fields are only going to be farmland, industry, lake, woodland). If you are not sure what the fields will be, then you would have to use a Dynamic writer. That's a bit more tricky (I was going to leave it out, but I figure it's worth documenting)...

2a) In the AttributeManager, also create attributes name (as a copy of landuse_category) and fme_data_type (set to your data type, I used fme_char(1))

2b) Add an Aggregator transformer. Set the Generate List option. Call the list attribute and add name and fme_data_type:

btw, you could use this instead of your method to get a schema definition. If you check the output of this you will see a schema defined as a list.

2c) We now need to get the schema definition onto all the features. Do this with a FeatureMerger transformer. Set it up like this with the requestor/supplier set to a constant value (like 1 in my case):

2d) Set the writer to Dynamic mode, using Schema from Schema Feature as the schema source:

Now you have a truly dynamic solution. If a landuse type changed (from farmland to agriculture perhaps) or a new landuse type was added (say urban) or one was deleted, then the workspace will operate automatically without needing to be edited.

Answer 2

I want to add another answer here, because I got a fix into the product that makes this easy to do (the first answer is still valid, but this is better).

The AttributePivoter transformer in FME is used for creating pivot tables. Once use of these is to flip the data so that row values become the column headers, which is what we want here.

So, set up a translation in FME, from CSV to CSV, and add an AttributePivoter transformer between the two tables:

Open the AttributePivoter parameters and set it up as follows:

• Group Rows By: ID
• Group Columns By: landuse_category
• Attribute to Analyze: landuse_category
• Pivot Summary Statistics Types: Count
• Row Group Summary Line Descriptor: Total

That will rearrange the data and create a schema for use. So, open the parameters for the writer Feature Type. Set:

• CSV File Name: The name you want for the output file
• Dynamic Schema Definition: Yes (check box)
• Schema Sources: "Schema From Schema Feature" (only that)
• Attributes to Remove: Grand Total (you'll need to type that in manually)

Go to the User Attributes tab and delete any attributes:

Change the delimiter character for the output (parameter in the Navigator window) if you wish, and you're good to go.

So just one transformer and a little dynamic schema setup. Much easier than FeatureMerger and Aggregator transformers. Because it relies on a small fix to FME, you'll need FME2018.1 or newer (officially that will release in a couple of days I believe) - else you'll end up with a Grand Total attribute that you can't get rid of.