Business Intelligence Tools / Developers Corner

TERR in Spotfire — passing marked data through to another table

  • Have you wanted to to limit calculated columns to a smaller data set but been unable to because calculated columns always take into account the entire data set?
  • Have you ever wanted IF statements to be more dynamic?
  • Would you like to build filtering or data reduction into a workflow but still maintain the original data connections?

In today’s post, I am going to show you how to use TERR to pass rows and columns through from one table to another.  There are probably many good use cases for this functionality, but I will focus on two.

  1. Building a predictive model
  2. Creating calculations on a subset of data

Building a predictive model — When running predictive models in Spotfire (Regression, Classification and most templates I’ve seen), the models will run on the entire data table. Filters are not applied.  However, it is quite often the case that users need to filter or remove data, be it for nulls or values outside an allowable range or perhaps the user simply wants to run a model for a certain type curve region or area of the field.  That’s when being able to pass through specific records to another table is extremely helpful.  Passing records through to a second table and then connecting the second table to the model allows the user to maintain the original data source, set and connections.  Additionally, as part of process, the user can decide whether they want to pass all columns or only a few, which can reduce the number of columns in a drop down list or selector.

Creating calculations on subset of data — When calculated columns are created in Spotfire, they always take into account the entire data set and are NOT inclusive of filtering.  This can be worked around in DXPs by writing expressions on the y axis of a visualization or by incorporating property controls into calculations, but these are time consuming workarounds.  Another option is to pass a subset of data through to another table where the calculations are housed.

This is all done with an extremely simply TERR script.

  1. Go to the Edit menu>Data Function Properties> Register New
  2. Name the script, something like “Pass through”
  3. Set the type as R script – TIBCO Enterprise Runtime for R
  4. Enter this script in the script tab (as shown in the screenshot below) — output <- input 
  5. Go to the Input parameters tab, click the add button
  6. Name the input parameter ‘input’, name the display name ‘input’.
  7. Set the Type: to Table, click the all button to all data typesterr-input-parameter
  8. Go to the output parameters tab, click the add button
  9. Name the output parameter ‘output’, name the display name ‘output,’
  10. Set the Type: to Tableterr-output-parameter
  11. Click the run button
  12. If Spotfire asks you about saving, this is optional.  You may save the script in the Library or you may keep it embedded in the file.
  13. In the Input page, take note of the Refresh function automatically checkbox, users may want to check it depending on the workflow…then…
  14. Select Columns as the input handler.  Then users may select which columns are passed through in one of two ways….
  15. Set the radio button to Columns and select which columns the new data table should have OR
  16. Set can set the radio button to Search expression and enter ‘*’ in the expression box, and all columns will be passed through.  terr-input-columns
  17. Then, after this selection has been made, if the user would like to use marking or filtering to limit to a subset of records, choose a Marking or Filtering scheme (as shown below) in order to further limit the data passed through to the new table.terr-marking-or-filtering
  18. Go to the Output page
  19. Set the output handler to data table
  20. Set the radio button to create new data table terr-output-table
  21. As soon as the dialog is closed, there should be a new data table called output.  If marking or filtering was used to limit data, make sure this is clearly described and setup in the DXP.

In the example shown below, I have created two scripts, one attached to marking and another attached to filtering.  Watch the short video to see the script in action.

Update: 10/20/16

I found an even better use for this function today that I wanted to share.  I was working with a user who had facie data in box blots.  He had one box plot showing data by each individual facie, but then he wanted to be able to select a subset of facies and show a box plot for the combined facie data.  We used the code above in conjunction with a treemap.  The treemap had no size by variable and is connect to a single level hierarchy.  The treemap also has its own marking, and when the user marks each facie, that data is sent to the new table, which the box plot is connected to.  This is a huge improvement over the IF statements in calculated columns that we put together at first.  This same result can also be achieved with the subsets function, but the user liked the look and feel of the treemap “buttons”.



14 thoughts on “TERR in Spotfire — passing marked data through to another table

  1. Varun Reply

    how to single connect line of one of the statisitical values of the box plots in a boxplot chart in spotfire ?

    • Julie Schellberg of Big Mountain Analytics, LLC Post authorReply

      You cannot connect lines or markers in a boxplot. You can only add individual lines or markers. You cannot connect them.

  2. Thep Reply

    There is a slight deviation in step 4 between what is in the screen shot and the text. The one in the text is correct.

    • Julie Schellberg of Big Mountain Analytics, LLC Post authorReply

      Fixed. Thank you.

  3. Ankur Reply

    I am trying to add a column only to the filtered data(Spotfire is taking master data to apply functions on)
    I can use the R script method you just presented but everytime my filters change, I have to run the script by providing a button to it.
    is there a way around it?
    that the script gets initialized everytime the filters changes or everytime the output file changes

    • Julie Sebby Post authorReply

      I just saw this comment. I don’t know how I missed it, but there is a box you can uncheck so that script won’t run everytime the filters change. Then, you can attach the data function to a button so that the data function executes when you click the button.

  4. Ankur Reply

    I got that.
    I did not know that auto refresh option for the R scripts.

  5. Ch3rry Reply


    Could you please let me know how to assign the Maximum (Largest) value of selected multiple Bars in a Bar chart to a Document Property? Probably using R.

    Your help is much appreciated! Thanks.

  6. Pingback: Adding Calculated Columns to a Spotfire Table Using the Within Function in TERR • The Analytics Corner

  7. Pingback: How to Learn the Spotfire Expression Language » The Analytics Corner

  8. Anthony Reply

    Hey Julie,

    Any way to apply a filter to a particular field? Like if I want to exclude all rows that have N in a column.

Leave a Reply

Your email address will not be published. Required fields are marked *