| Exercise 3 | Precipitation Calculations |
| Data | Precipitation Data (Microsoft Excel) |
| Overall Goal | Calculate monthly precipitation |
| Demonstrates | Adjacent Feature Attributes |
| Start Workspace | None |
| End Workspace | C:\FMEData2016\Workspaces\DesktopAdvanced\Attributes-Ex3-Complete.fmw |
You're working on a project mapping monthly precipitation (rainfall) in the city. You have been given a dataset like so:
| Month | Precipitation |
|---|---|
| Jan | 168 |
| Feb | 273 |
| Mar | 387 |
| Apr | 476 |
| May | 541 |
| Jun | 595 |
| Jul | 631 |
| Aug | 668 |
| Sep | 719 |
| Oct | 840 |
| Nov | 1029 |
| Dec | 1191 |
Unfortunately, the numbers are a cumulative amount and you wanted to map individual figures for each month.
Rather than reaching into your desk drawer for a calculator, you decide to use FME to do the calculations!
1) Create Workspace
Create a workspace to translate the data as follows:
| Reader Format | Microsoft Excel |
| Reader Dataset | C:\FMEData2016\Data\ElevationModel\Precipitation.xlsx |
| Writer Format | Microsoft Excel |
| Writer Dataset | C:\FMEData2016\Output\Training\MonthlyPrecipitation.xlsx |
When creating the workspace, check the parameters for the Reader to ensure FME is recognizing the headers at the top of each column.
2) Add AttributeManager
To calculate precipitation for any given month you just need to subtract the previous month's cumulative total from the current month's cumulative total.
With FME you can use the Adjacent Feature Attribute functionality to fetch the previous month's number.
So, place an AttributeManager transformer between the Reader and Writer feature types:
3) Set AttributeManager Parameters 1
Open the AttributeManager parameters dialog.
Expand the advanced set of attributes and check the box marked Enable Adjacent Feature Attributes. In the fields provided enter 1 for the Number of Prior Features to be kept.
Next set the parameter Substitute Missing, Null and Empty by: to Default Value and enter 0 into the Default Value field:
| Professor Lynn Guistic says… |
|
The substitution parameter is more important than perhaps most people recognize.
Think about it: the first feature to be processed can’t have a prior feature, and the last feature to be processed won’t ever have a subsequent one. Therefore you always have to be careful in what you have set here. In this exercise we’re calculating a numeric value; therefore it makes sense to use 0 (zero) as the default replacement. |
4) Set AttributeManager Parameters 2
Now let's calculate the new precipitation value.
In the Attribute Value field for the precipitation attribute, click the drop-down arrow and open the Arithmetic Editor:
In the arithmetic editor dialog use the menu on the left to select:
- The FME Feature Attribute Precipitation
- The Math Operator – (minus)
- The FME Feature Attribute Precipitation for feature[-1]
All of which should leave you with an expression looking like this:
Now you can see why it was so important to set the substitution field, because it’s uncertain what result would occur from the above when feature[-1].Precipitation is missing!
Click OK to close the Arithmetic Editor dialog, and then click OK again to close the main AttributeManager dialog.
5) Save and Run Workspace
Save the workspace and then run it. Inspect the output.
The numbers start out looking correct, but quickly become wrong. Not even in Vancouver (I mean, Interopolis) does it rain 623mm in a single month!
The problem is this: unlike other occasions in FME, here we can’t simply overwrite the attribute we are working with. That’s because it skews the next calculation. i.e. the calculation for March needs to operate on February's original number, but instead it receives the value we've just overwritten it with!
The only way to solve this is by creating a new attribute.
6) Adjust Workspace
Return to the workspace. Edit the Writer schema by renaming the destination attribute Precipitation to MonthlyPrecipitation:
Now return to the AttributeCreator and change it to create an entirely new attribute called MonthlyPrecipitation:
It's a pain to have to do, but blame me for leading you in the wrong direction at first! You can't even just rename Precipitation to MonthlyPrecipitation since, whatever you call it, it still fetches an incorrect value.
In fact, it might be quicker to just remove the transformer and replace it with a new one, instead of trying to copy the calculation to a new attribute.
| WARNING |
This should solve the problem. But! You should check the attribute mapping that results from this operation. In my workspace it looked like this:
That's obviously incorrect and you need to make sure it is MonthlyPrecipitation that is being mapped, not Precipitation: 
|
7) Re-Run Workspace.
Save the workspace.
Before you re-run the workspace, check the Writer Parameter called "Overwrite Existing File" in the Navigator window.
Set it to Yes – if it isn’t already – so the output overwrites the destination dataset and doesn’t just append this data onto the same spreadsheet.
Also make sure the file you are writing to is not already open in Excel (or any other editor).
Re-run the workspace.
Inspect the output. This time the numbers should be correct:
| CONGRATULATIONS |
By completing this exercise you have learned how to:
|