Scenario
In an Excel table, I can add a new column that contains a formula to calculate values using data on other columns. How do I do this in PhixFlow.
Concepts
In a model, data is loaded into streams from external sources, such as files and databases. Usually we then want to calculate additional values and use them in subsequent streams. Key concepts to be aware of:
- In PhixFlow, you use an expression to run calculations on data in order to generate different data from it.
- Expressions refer to attributes, which are the equivelant of column headers in Excel. When you run analysis, the calculation specified is run on every record.
- PhixFlow processes attributes in the order they are listed in the stream. If you want to use a value from an attribute, it must appear earlier in the attribute list in the stream.
Solution
- Create the stream and connect it to its data source or input stream.
- Run analysis to check that the input data is loading into the stream correctly.
At this point you can optionally run rollback to clear the data; see Rollback 13 Rolling back Streams. - Now add the new attributes to the stream. These are like the Excel columnsThis is the same as adding a new column to an Excel table. Remember to set the correct data Type and Length.
Make sure the order the Order number is greater than the attributes loaded via the input pipe.
Tip you want to use
a value from another attribute it must appear earlierin the
attribute listcalculation.
- In the Expression field, write the expression to calculate the value. This is the equivalent of the Excel formula. Specify the value to use in the calculation with To tell PhixFlow to use values from another attribute, specify its name using:
_out.<name>
For example, to set the attribute have a value that is 75% of the value in an earlier attribute, write the expression:_out.<name> /0.75
For information about:
rolling back streams, seeExample
A stream contains an attribute called GrossValue. The new attribute called NetValue, should be 75% of the gross.
In NetValue's expression, enter: _out.GrossValue /0.75
| Tip |
|---|
Another way to pass values between attributes and streams is to write an expression that calculates a $-variable. These are most useful when you need to look-up data or trigger actions that depend on the context. |
See also
- rolling back streams: training module 13 Rolling back Streams and Rollback
- expressions , see and $-variables: Scripting and Scripting Basics
- Context Parameter
Context parameters
Which country
Which Date
Ref _context.CountryCode - relates to the parameter
Calculation in expression to give a $ variable- values that depend on context: Context Parameter
