Files
Introduction
In this tutorial, you learn how to calculate values using the ExpressionEvaluator and how to assign values using the AttributeValueMapper.
Data Source
For exercise 1, the source dataset is a CSV (comma-separated values) file containing population information for each neighborhood in the City of Vancouver.
The source dataset for exercise 2 is a Microsoft Excel spreadsheet containing drinking fountain locations in the City of Vancouver.
Step-by-step Instructions
Exercise 1: Calculating Values using the ExpressionEvaluator
In the first exercise, you will calculate population change over a 10-year period. This requires a simple mathematical operation between two FME Feature Attributes that can be performed using the ExpressionEvaluator.
1. Generate Workspace
In FME Workbench, generate a new workspace.
In the Generate Dialog, set the Reader Format to CSV (Comma Separated Value), then browse to the NeighborhoodPopulation.csv dataset that is available in the Files section of this article. There are no parameters to set.
Next, set the Writer Format to CSV (Comma Separated Value), then browse to an Output folder. Click OK to finish generating the workspace.
2. Set Output Name
After the workspace is generated, double-click on the CSV writer feature type to open the parameters.
In the parameters, change the CSV File Name to NeighborhoodPopulation. Confirm that Overwrite Existing File is set to Yes.
3. Update Attributes
Still in the writer feature type parameters, switch to the User Attributes tab, then set the Attribute Definition to Automatic. Click OK to close the dialog.
Switching the Attribute Definition to Automatic will automatically update the writer to include any attribute changes we make in the workspace, such as adding a new attribute (column).
4. Calculate Values
Now we need to add an ExpressionEvaluator to create a new attribute (column) that will contain the calculation. Click on the connection line between the reader and writer feature types to select it. Then add an ExpressionEvaluator to the canvas by typing “ExpressionEvaluator” to bring up the list of FME Transformers in the Quick Add Search. Select the ExpressionEvaluator from the list of Transformers by double-clicking or by using the arrow keys and the Enter key to add it.
The ExpressionEvaluator performs a mathematical calculation on an expression that consists of FME Feature Functions, String Functions, Math Functions, and Math Operators. In this case, we will use it to determine the population change from 2001 to 2011 for each neighborhood.
Double-click on the ExpressionEvaluator to open the parameters. In the parameters, confirm that Evaluation Mode is set to Create New Attribute, then set the Output Attribute Name to PopChange.
FME allows you to access attributes from any of the datasets that are connected to the ExpressionEvaluator via the FME Feature Attributes list. To calculate population change, subtract the 2001 population from the 2011 population - both of which are attributes stored in the table. To do this, expand the FME Feature Attributes List, then double-click on TotalPopulation2011 to add it to the expression. Next, type the subtraction operator (-), then add TotalPopulation2001. Optionally, you can copy and paste the following expression: @Value(TotalPopulation2011)-@Value(TotalPopulation2001)
5. Run Workspace
Confirm the new PopChange attribute (column) will be added to the table by clicking on the arrow on the writer feature type to expand the attributes. There should now be the PopChange attribute.
Run the workspace by clicking the Run button on the top toolbar, or by selecting Run > Run Workspace from the top menu bar.
After running the workspace, the table will have a PopChange column with the calculated population change. You can view the new table by selecting the writer feature type to open the mini toolbar and clicking on the View Written Data button.
The data will open in Visual Preview, where you will see the newly added column.
Exercise 2: Assign Values Based on Another Attribute
In this exercise, we will assign values based on the maintainer attribute using the AttributeValueMapper. The AttributeValueMapper will look up the source value of the maintainer attribute and assign the destination value (department code) to a new field. This will allow us to join tables to the DrinkingFountains table using the department code as the primary key.
1. Generate Workspace
In FME Workbench, generate a new workspace.
In the Generate Dialog, set the Reader Format to Microsoft Excel, then browse to the DrinkingFountains.xlsx dataset that is available in the Files section of this article. There are no parameters to set.
Next, set the Writer Format to Microsoft Excel, then browse to the exact same DrinkingFountains.xlsx dataset, as we will be dropping the table and recreating it. Click OK to finish generating the workspace.
2. Set the Writer and Drop Mode
After the workspace is generated, double-click on the DrinkingFountains writer feature type to open the parameters.
In the parameters, confirm that the Writer Mode is set to Insert. Now expand the Drop/Truncate section and change the Drop Existing Sheet/Named Range to Yes. This will drop the old existing sheet, then re-create it with any new schema changes we make. If this were a database writer, we would change the Table Handling Parameter to Drop and Create.
If Drop Existing Sheet/Named Range was set to No, the data would be appended to the bottom each time the workspace ran, resulting in duplicate records.
3. Update Attributes
Still in the writer feature type parameters, switch to the User Attributes tab, then set the Attribute Definition to Automatic. Click OK to close the dialog.
Switching the Attribute Definition to Automatic will automatically update the writer to include any attribute changes we make in the workspace, such as adding a new attribute (column).
4. Inspect Values
Before mapping attribute values, we need to know the attribute values we are dealing with. Click on the DrinkingFountains reader feature type to open the mini toolbar, then click on View Source Data.
In Data Preview (formerly Visual Preview), note the value for the maintainer attribute: either Engineering or Parks.
5. Assign Values with the AttributeValueMapper
Now that we know the values, we need to add an AttributeValueMapper to map them. Click on the connection line between the reader and writer feature types to select it. Then add an AttributeValueMapper to the canvas by typing “AttributeValueMapper” to bring up the list of FME Transformers in the Quick Add Search. Select the AttributeValueMapper from the list of Transformers by double-clicking or by using the arrow keys and the Enter key to add it.
Double-click on the AttributeValueMapper to open the parameters. In the parameters, set the Input Attribute to the maintainer attribute, then set the Output Attribute to deptcode.
Next, under Input Value, type Engineering, then under Output Value, type 300, which will be the deptcode. Repeat this for Parks and an Output Value of 246.
6. Run Workspace
Confirm the new deptcode attribute (column) will be added to the table by clicking on the arrow on the writer feature type to expand the attributes. There should now be the deptcode attribute.
Run the workspace by clicking the Run button on the top toolbar, or by selecting Run > Run Workspace from the top menu bar.
After running the workspace, the table will have a deptcode column with the assigned values. You can view the new table by selecting the writer feature type to open the mini toolbar, then clicking the View Written Data button.
The data will open in Visual Preview, where you will see the newly added column.
Data Attribution
The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.