Add Attributes with Calculated Data

Owned by Fiona Sargeant (Unlicensed)
Last updated: Jun 07, 2022 by Anthony George
2 min read

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

  1. Create the table and connect it to its data source or input table.
  2. Run analysis to check that the data is loading into the table correctly.
    1. At this point you can optionally run rollback to clear the data.
  3. Now add the new attributes to the table, set the following:
    1. Name, of the attribute. This will be used to reference this attribute in other expressions.
    2. Type, the data type pf the attribute such as String or Decimal.
    3. Length, specifies the length of the string if the data type is selected.
    4. 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.
    5. 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 the in prefix 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

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