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

enter image description here

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?


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:

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

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.

  • 1
    The other solution I should mention is the AttributePivoter transformer. It works very simply (see screencast.com/t/MrkWqvWX for screenshot) but has the drawback that it adds an extra field (Grand Total) with no good way to remove it. I just add this comment to give full info. If you can live with the extra field then it's an easier way to go. Otherwise the above would be better I think. – Mark Ireland Jun 13 '18 at 15:47

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:

enter image description here

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

enter image description here

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)

enter image description here

Go to the User Attributes tab and delete any attributes:

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.