In this article, we'll show you how to use the Calculate Fields node, and then how to handle errors that will occur as you build a dataflow. First, we'll build a working example of the Calculate Fields node and then we'll cause it to fail.
The Calculate Fields node is used to add or replace fields in the data stream with new values based on a calculation. For more information on the node, see here.
BUILDING A DATAFLOW USING THE CALCULATE FIELDS NODE
1. Search for the Create Data node in the node panel and drag it onto the canvas. Do the same thing for the Calculate Fields node
2. Run the Create Data node and connect it's output pin to the input pin on the Calculate Fields node
3. In the properties panel under “Calculated Fields” add the following:
- Set Name to “half rand”
- Set its Type to double. The term double is short for “double precision floating point number” referring to a common standard strategy for saving real numbers (non-integers) in a computer. We usually use this type for money.
- Set the Expression to “rand/2”.
4. Run the node and view the output.
The new field, “half rand” is the first field in the 20 record output. Any errors that occurred during calculations will be sent to the second output pin.
The fields Variable
5. In the Calculate Fields node properties, in the line below “half rand” add a new field
- Name it “X”
- Set the Type to double.
- Set the Expression to: “fields['half rand'] + IOU”.
Notes: We can reference IOU directly, but we must use fields to reference “half rand”. This is because it contains a space. Any reserved words or fields with spaces or punctuation in their name must be referenced this way.
6. Re-run the Node
Re-run the node by going to the Tool Bar, to the right of the Run button is the “Re-run” button, , which looks like a green arrow circling around the play button indicating re-play. After it runs, view the output.
ERROR HANDLING & TROUBLESHOOTING
To practice with error handling with this node and in general, do the following:
7. Add another field below “half rand”.
- Set Name to “test”
- Ignore the Type field leaving it as Unicode. Unicode refers to a strategy for storing characters. We are ignoring it because we don’t plan to use this value for anything, so it won’t matter if it is output as characters instead of a number.
- Set the Expression to “5 / IOU”.
8. Re-run the node. See that we now have 4 errors and the remaining records are in the first output. Every time the IOU value is zero a “division by zero” error occurs and those records are sent to the second output.
9. Examine the 4 output records by clicking on the errors output pin. You can see the reason for the error in the 5th field named _ErrorMessage
Notice that the node is splitting the errors out of the data stream. Very often this is undesirable and you prefer to be notified of any errors processing the data. Lets change the configuration on the node to throw an error we cannot ignore when this occurs.
10. In the Calculate Fields node properties panel, click the triangle next to Exception Behavior to open up that section of properties
11. Set the ErrorThresholdCount to 0 so that it will fail at the first error that occurs in a calculation
12. Re-run the node, and the node will error.
13. Go over to the Node Panel and click on the Errors tab to see information about the error
14. Scan through the error information in the tab. You can View the input and output node data from the node run up to the point of node failure. Click View node log to get a lot more details about the error.
15. Change the ErrorThresholdCount to 3 and re-run the node. This way the node will allow up to three errors to occur without throwing an error. Or clear the value out and there are no limits to the number of errors