Scenario
We want to use data to evaluate a new set of values and to add the values to a new attribute.
Stream with list of attrs → stream + attr
Calculation in expression to give a $ variable
Solution:
- how to
...
| Insert excerpt | ||||||||
|---|---|---|---|---|---|---|---|---|
|
Overview
In a spreadsheet such as MS Excel, I can add a new column that contains a formula to calculate values using data in other columns. How do I do this in PhixFlow?
In a PhixFlow analysis model, data is loaded into tables from external sources, such as files and databases. Usually we want to calculate additional values and use them in subsequent tables. 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.
- The expression is in an attribute, which is like a column header in Excel.
- When you run the analysis model, PhixFlow runs the calculation on every record, adding the data to each cell in the attribute's column.
- PhixFlow processes attributes in the order they are listed in the table. If you want to use a value from an attribute, it must appear earlier in the attribute list in the table.
Solution
- Create the table and connect it to its data source or input table.
- Run analysis to check that the data is loading into the table correctly.
- At this point you can optionally run rollback to clear the data.
- Now add the new attributes to the table, set the following:
- Name, of the attribute. This will be used to reference this attribute in other expressions.
- Type, the data type pf the attribute such as String or Decimal.
- Length, specifies the length of the string if the data type is selected.
- Order, sets the position of the new attribute in the table. If left black the attribute will be added to the end. Attributes can only reference the processed values of attributes which appear before this one.
- Expression, write the expression to calculate the value. This is the equivalent of the Excel formula. To tell PhixFlow to use processed values from another attributes, specify its name using:
_out.<name>. Use theinprefix to use unprocessed values.
Example
A table contains an attribute called GrossValue. A new attribute for the net value should be 75% of the gross.
Create the attribute NetValue and in the expression, enter: _out.GrossValue * 0.75
| Tip |
|---|
Another way to pass values between attributes in a table is to write an expression that calculates a $-variable. These are most useful when the value you want depends on context. This can be the case when looking-up data or triggering actions. |
More Information
- For commands that are available to use in an expression see Functions
- rolling back tables: training module 13 Rolling back tables and Rollback Recordsets
- expressions and $-variables: Using Variables and Expression Basics