Easy TERR — Generating a List of Unique Values from a Table

  • Would you like to create a new table based on unique values from a different table?
  • Would you like to learn how to reference a column in a TERR data function?
  • Are you combining data from different sources and having problems with duplication?

My Project

I am currently working on a project that joins oil and gas header data from three different data sources (via an insert rows operation). Then, that data gets put onto a map. The user marks the map, which then executes an on-demand query to pull in the production data for only marked wells. With this architecture, the file opens quickly and only brings in production data for the wells needed.

Here’s a pictoral look at the architecture. (Note: Originally, more was happening in the starter header. It could be eliminated).

The Problem

This is a pretty good architecture and workflow with one problem. The problem is the wells can be on the map three times. When the user marks the map, Spotfire generates an on-demand query that may include duplicate API numbers. That’s inefficient. I even went into the SQL logs to look at the query generated, and I found those duplicates.

The Solution

I can still use my existing architecture and improve the project by using a TERR data function to create a list of unique APIs from the master header table. Then, I can populate the map from this new, smaller table. Users will still mark the map, but when they do a more efficient query is generated.

All it takes is a simple line of code using the unique function. Now, if you’ve never created a TERR data function before, I suggest this read on TERR basics. I’m not going to show every TERR screen, but if you understand the basics explained in that post, you’ll be fine. Here is what the code looks like.

TERR Code

uniqueAPI10 <- unique(c(masterheader$API10))

unqiueAPI10 and masterheader and both objects and parameters. They are objects I created, and I must create parameters to map them to the data in Spotfire. Unique is a R/TERR function.

masterheader$API10 points to the API10 column in my table.
Here, I created the masterheader parameter. The input Type is “Table”.
Here, I created the uniqueAPI10 output parameter. Again, the output Type is “Table”.

After clicking run, Spotfire will ask you to map the parameters to the data. I mapped the masterheader object to my master header table, only bringing in the API10 column because that’s all I needed. Then, I told it to create a new table called uniqueAPI10. Now, I have a new table with only the unique API10 values.

All said and done, this is a simple example, but it shows you how to use a R/TERR function and access a specific column of data.

Spotfire Version

Content created with Spotfire 7.12.

Leave a Reply

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