Using Table Maths
Table Maths generates a new table of data from an input table by applying formulas over each row. The number of rows in the output table will exactly match the number of rows in the input table.
Input table: Click and select the table to use.
- If a value is missing in a row of the input table, then the value of the variable for that column will be NaN if it is a numeric or flag column, or the empty string if it is a string column.
- Click the blue (+) button to add new columns to the output table.
Column Name: holds the name of the new column to be generated in the output table.
- If the name of the column is not a valid variable name within formulas, then it may be renamed for use within the formula. If a column is renamed for use in the formula, then a warning will be displayed below the input table selector with the variable names that you can use to reference the renamed columns.
- Output Type: allows you to set the type of data that the formula produces. The output type can be left as auto, and the process will try to automatically determine the type of values that the formula is producing. Otherwise, you can explicitly select an output type, and the values that the formula produces will be converted to that type.
- Formula: holds the formula to be applied to generate the values for the column. This formula is applied on every row of the input table. The columns of the input table can be referenced by name for use within the formula. The syntax and rules for writing math formulas are explained later in this document.
- Click the Open Table Viewer button to view the current maths table.
If you are re-using the same value multiple times, or if you wish to organise your equations, defining your own variables can be very useful (and sometimes even more efficient!).
- The variable name appears on the left, followed by an equals sign (=), your expression, and finally a semi-colon (;).
a = v1 * 2; res = a + sin(v1);
- The semi-colon is very important. It must be there to separate the declaration of each variable.
% Modulo: A%B yields the remainder of A divided by B
^ Power: A^B raises A to the power of B
In the absence of any operator, multiplication is assumed. Thus, 5 a will be interpreted as 5 * a. For other operators, see also the Conditions sections for boolean operations, and the Bitwise Operators section below.
Maths operations support a number of bitwise operators. These can be useful for setting flags in headers, but these are not boolean operations. Some care must be taken:
- Only the integer part of a value is considered
- Operations consider which bits are set in that integer value
As a consequence, these differ from conditional operations (if, && etc) which treat any non-zero value as one (true)
a xor b Exclusive OR: sets bits in the output only where they differ in the inputs, according to the following table.
a & b Bitwise AND: performs a logical AND on each bit in the inputs, and sets the corresponding output bit to the result.
a | b Bitwise OR: performs a logical OR on each bit in the inputs, and sets the corresponding output bit to the result.
~a 1's complement: performs a bitwise NOT, inverting all bits in the value. Note that the integer representation is signed, so this is equivalent to (-a - 1).
* Simplified to show an 8 bit representation
You can build complex expressions that result in different behaviour in different situations. Conditional statements take the form:
if ( condition , true statement , false statement )
where the true statement is used if the condition is true, or vice-versa. Note that those statements can themselves be arbitrary expressions, such as another conditional statement.
Standard comparison operators may be used, which may be combined with standard boolean operators. You can preface any boolean expression with a ! to negate it:
Note: Do not use != nan or == nan to check for invalid values in conditional statements. It won't do what you think! Use one of the isnan(), isnull(), isvalid(), etc. functions instead.
if (a < 0 && b < 0, c, a + b)
If both a and b are less than zero, use the value of c; otherwise use the sum of a and b
if (a < 0, if (b < 0, c, a + b), a + b)
A more-complicated way of writing the same thing, using one conditional inside another.
if (isnan(a) || isnan(b), 1500, a + b)
If either a or b are NaN (invalid), use 1500; otherwise use the sum of a and b
The most natural-seeming way to merge two things together would be to "add" them, as in a + b + c. But this doesn't work, for two reasons.
First, in places where they overlap, that would give you the arithmetic sum of the values at that location -- which is probably not what you intend.
Second, missing values are generally represented by the value NaN (not-a-number). NaN plus any other value remains NaN, so this would have the effect of producing output only at locations where all inputs are defined. This is also probably not what you intend.
To simplify this, Insight provides several functions to make combining easy:
- merge(a, b, c, ...) - combines any number of items, in order of precedence. i.e. where they overlap, the first available value in the list will be used.
- min(a, b, c, ...) - combines any number of items, using the smallest available value
- max(a, b, c, ...) - combines any number of items, using the largest available value
- sum(a, b, c, ...) - arithmetic sum of any number of items, ignoring those that aren't defined at a location
- avg(a, b, c, ...) - arithmetic mean of any number of items, ignoring those that aren't defined at a location
- random(max) - generate a random number between 0 and max (inclusive)
- random(min, max) - generate a random number between min and max (inclusive)
- lerp(v0, v1, t) - linearly interpolates between two inputs (v0, v1) for a parameter (t) in the closed unit interval [0, 1]. Parameter t is clamped between 0 and 1.
You can use comments to explain your formulas to other users.
- Comments are started by a hash #, and extend to the end of that line. Anything written within a comment will be ignored in the formula.
# Comments can appear on their own line.
a = v1 * 2;
res = a + sin(v1); # Or at the end of other lines.