Expression function only returns value for first row in Spotfire

Product: Spotfire Analyst
Versions:All

Summary:
This article describes how to ensure that an expression function returns values for all rows - not just the first row.

Details:
When using an Expression Function in a calculated column, you may see a value returned only in the first row. You may expect this to return a value for every row, in the way many built-in Spotfire functions work.
 

 For example, if you have the following Expression Function (Edit > Data Function Properties > Expression Function) with the name mySum():

# Define custom function using R syntax, which will be run using the Spotfire desktop client's built-in TERR engine. This example is a basic sum:

mySum <- function( input )
{
    out <- sum( input )
    out
}

# Run the function to produce the output, repeat for length of original column
output <- mySum( input1 )

And a data set like:

category,value
A,1
B,2
C,4
D,7
E,2

Then your calculated column with the expression "mySum([value])" would result in:

category    value    mySum
A    1    16
B    2
C    4
D    7
E    2

Cause:
Think of the Expression Functions in the following way when looking to use them in a Calculated Column.
 - Input: You pass into the expression function any number of columns (per its definition).
 - Output: Your expression function should return one column, normally with the same number of rows as the original columns.

In the original example expression function, only a single value was returned, "16". Therefore, it was returned for the first row, and the subsequent rows were empty because there was no more output data.

Resolution:
 

To have your Expression Function based Calculated Column return a full column of data, you must ensure that the expression function returns a full column of data. This example uses TERR's rep() function to repeat the single result of the custom function for the length of the original input column (matching the number of rows).

# Define custom function. This example is a basic sum:

mySum <- function(input)
{
    out <- sum(input)
    out
}

# Run the function to produce the output, repeat for length of original column
output <- rep( mySum(input1), length(input1) )

Now the expression function returns a vector whose length matches the number of rows in the original input column, so the results are seen in each row, resulting in the following:

category    value    mySum
A    1    16
B    2    16
C    4    16
D    7    16
E    2    16

Also, starting in Spotfire version 7.7, there is an 'Aggregation Function' type of expression function which allows easier calculation of aggregated data using Expression Functions.