FME Version
Files
Introduction
In this tutorial, you learn how to calculate values using the ExpressionEvaluator and how to assign values using the AttributeValueMapper.
Step-by-step Instructions
Exercise 1: Calculating Values using the ExpressionEvaluator
In the first exercise, you will calculate population change over a 10 years period. This requires a simple mathematical operation between two FME Feature Attributes that can be performed using the ExpressionEvaluator.
Source
The source dataset is a CSV (comma-separated value) file containing population information per neighborhood in the City of Vancouver.
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 change in population 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 subtract 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 on the Run button on the top toolbar, or by using Run > Run Workspace on 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 popup menu 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 value of 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.
Source
The source dataset is a Microsoft Excel spreadsheet containing drinking fountain locations in the City of Vancouver.
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 was a database writer, we would change the Table Handling Parameter to Drop and Create.
If Drop Existing Sheet/Named Range was set to No, every time the workspace is run, the data would be appended to the bottom, resulting in duplicated 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 first need to know the values of the attributes we are dealing with. Click on the DrinkingFountains reader feature type to open the popup menu, then click on View Source Data.
In Visual Preview, take note of the values for the maintainer attribute, which is either Engineering or Parks.
5. Assign Values with the AttributeValueMapper
Now that we know the values, we need to add an AttributeValueMapper map to those values. 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 in Engineering, then for Output Value, type in 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 on the Run button on the top toolbar, or by using Run > Run Workspace on 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 popup menu and clicking on the View Written Data button.
The data will open in Visual Preview, where you will see the newly added column.
Data Attribution
Data used in this tutorial originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.
Comments
0 comments
Please sign in to leave a comment.