Lookup Tables

Description

Lookup Tables allow you specify multiple columns of data and transform a scalar or a signal by substituting its value or values from one column with the value from the same row in another column.

For example, using the Lookup Table below we can transform error codes into descriptions or response behaviors. These transformations happen to each sample in a signal, with customizable interpolation options for numeric signals.

Written in Formula this would be:

$errorCodeSignal = ...
$table = lookupTable(
"[
  [0, 'Condition Normal', 'None'],
  [1, 'Overheating module', 'Activate cooling'],
  [2, 'No current', 'Examine wiring'],
  [3, 'Unstable voltage', 'Examine wiring'],
  [4, 'Liquid detected', 'Turn off and let liquid evaporate']
]", InterpolationMethod.Step)
$table.lookup($errorCodeSignal, 'A', 'B')

Using a lookup table has two parts.

1. Specifying the Lookup Table

The lookupTable function is used to create a lookup table. It takes two inputs.

• tableDescription, a string scalar that defines the data of the table. It is a string containing a matched set of brackets [], where each row of the table is entered as a pair of brackets containing comma separated scalar values. Rows are separated by commas.

  • The table can be entered on a single line or in multiple lines, whatever is easiest for the user.

  • The first row defines the units of the columns. All other units must match the first row in their respective column. Units can be left off of subsequent rows and will be coerced to match.

  • Strings are valid scalar inputs. They must be placed in quotes. If the entire table string is enclosed in double quotes, strings inside must be in single quotes and vice versa. The following tables would function identically.

    CODE

    "[[3m, 'Three Meters'], [500cm, 'Five Meters']]" 

    A single line tableDescription with units fully specified. Uses double quotes for the entire table string, and double quotes for internal strings.

    CODE

    '[                        
    [3m, "Three Meters"],
    [5, "Five Meters"]
    ]'

    A multi-line tableDescription with units in the first row. Uses single quotes for the entire table string and double quotes for internal strings.
    

• interpolationMethod, the interpolation method used for non-exact matches of the table. The possible options are

  • Linear or PILinear: linearly interpolated using the nearest keys and their values.

  • Discrete: no interpolation is used. Keys that have no match in the table will return an invalid sample.

  • Step: the value of the nearest smaller key will be used.

  • Note that if a table contains strings it must use either Discrete or Step as an interpolation method.

  • Note that no extrapolation will ever be performed (if the key is greater than or smaller than all values in the target column, the returned value will be invalid).

  • No interpolation will be performed if the table is not already sorted in ascending or descending order by the column with the values to be interpolated between. Instead, an error will be returned.

Scalars within the key column must be unique (the target column is allowed to have duplicate values).

2. Lookup a signal using the table.

The lookup function is used to perform the lookup. It takes 4 inputs.

• table: the table created in step 1.

• keys: the signal being looked up. Must match the units of the keyColumn.

• keyColumn: a string specifying which column to match the input against. Uses Excel indexing, so to indicate the first column of the table, use ‘A', to indicate the third column, use 'C’ and so on.

• targetColumn: a string specifying which column has the desired output. Uses Excel indexing.

$table.lookup($keys, 'A', 'B')

The lookup function will check every sample in the signal against the table. For each signal sample, its value will be looked up in the appropriate column of the table. If a match is found, the matching value in the other column will be added as a sample in the return signal. If the signal sample is invalid, an invalid sample will be added to the return signal. If the signal sample value is not found in the table, but is between values in the table, it will be interpolated according to the table’s interpolation method by finding the nearest values in the sorted column.

3. Lookup a scalar using the table.

The process for is the same for the signal except for swapping the signal parameter with a scalar parameter.

$table.lookup($key, 'A', 'B')

Reusing Lookup Tables

We can define the table string in a separate Formula, and use it as a variable in future Lookup. This lets us avoid having the string copied into every formula that uses it.

Current Limitations

• Scalars within the key column must be unique (key column being the one in which values are to be looked up).

• String values cannot contain commas.

• keyColumn, targetColumn and tableStringmust be Locally Constant Values.

  • At this time, the LookupTable itself cannot be exported as a variable for use elsewhere, only the tableString can.

• It is not possible to assign particular keys an invalid value.

• Extrapolation results in invalids.

• Columns on which interpolation is expected must contain values already sorted in ascending or descending order (must be monotonic).