Filter to Pasted Values in Spotfire

In this post I’ll show you how to filter to a custom list of values that you paste into Spotfire. This use case derives from a question I’ve been asked several times: “How can I paste a list of API numbers (from something like Excel) into Spotfire and quickly filter to those values.” For the non-oil and gas audience, an API number is just a unique identifier for an oil or gas well – it doesn’t have to be API number, it can be any data type, but this is a common use case for oil and gas.

This can be really useful when you have a data table with hundreds or thousands of unique values, and you quickly want to find a small group that you’re interested in. The problem is that finding the values in a filter can be very time consuming, and often times you have a list of values from some other software (excel, an email, etc) that you want to view in Spotfire. For example, let’s say you have Spotfire connected to competitor data from something like IHS or Drilling Info and someone sends you a list of wells that you need to evaluate. The easiest thing would be to just copy those wells and paste them into Spotfire, and have it automatically find and filter to those wells. This is possible, and it takes just a few minutes to configure!

In this blog post I’ll show you how to set this up in Spotfire very quickly using a Property Control and a Data Function using a very simple TERR script. You’ll end up with an input box that you can paste into, and some visualizations that will show the values that you pasted in, like the screenshot below. In this example I’ll be using API numbers, but you can input any list of values, whether it’s a well identifier like API, UWI, PROPNUM, etc, a list of Operators, or something completely different.


Download this Spotfire file for free here: https://exchange.ai/downloads/filter-pasted-values/. Even though it’s listed as “Subscriber Only”, you can login or setup a free Exchange.ai Portal account to download the template.

 

Here’s a quick overview of the steps:

  1. Add a multi-line input field to a text area
  2. Register a data function that converts the values from the input field into a Spotfire table
  3. Create a data table relation between an existing table and the pasted values
  4. Find the values you input through marking and / or filtering

Okay, now let’s dive into the details. Since the main purpose of this post is to teach the functionality of the “paste and filter to”, I’ll skip over the input box portion and assume you’re already comfortable using property controls. If not, you can reference some of my other posts for using property controls in Spotfire.

Here’s what the Property Control I setup looks like. Just add and edit a Text Area, and add the Property Control called Input field (multiple lines).

 

Create a Data Function

After adding the input box, register a new data function by clicking Edit > Data Function Properties, and then click the Register New button.

 

This will open the Script Editor where you can type / paste in the R code. Give your script a name, and then paste in the 3 lines shown below:

values <- paste(inputValues)
connValues <- textConnection(values)
outputTable <- read.csv(connValues)


 

Data Functions in Spotfire take inputs from Spotfire (properties, columns, tables, etc), do something with them (run some code), and then put the results back into Spotfire. So far, we have written the script that will take the pasted values and put them into a table, now all we have to do is hook up the inputs and outputs.

Select the Input Parameters tab and click Add. For the Input Parameter Name, use the same name used in the script, which is inputValues. The name can be whatever you want, just make sure you type the name exactly the same as in the script. Click the Type dropdown and select Value, since we’re pasting in a bunch of values into the input box. Next, check the String box for the Allowed data types. Feel free to give it a description, and then click OK.

 

Now we’ll make an output parameter for the resulting data table. Click the Output Parameters tab and click Add. Name the parameter outputTable (again, this can be called whatever you want, just use the same name in your script) and select Table for the Type.

 

After writing the script and creating inputs and outputs, we need to connect these things back to Spotfire. Click Run button in the upper right corner to try executing the script. That opens the Edit Parameters window where we can hook things up. Start with the Input tab, and you can see there’s only one input for this data function: inputValues. Select that one and click Document Property for the Input Handler. Then select the document property that you created for the input box – mine is called UserData.

 

Now click the Output tab, and we can see here there’s also only one Output: outputValues. Select that one, and then choose Data table for the Output Handler. Then click the radio button next to Create New Data Table. The name in this field will be the name of the new data table, so feel free to customize it now.

Be sure to click the button next to Register function automatically if you want to automatically run the data function every time new data is pasted into the box. You can also manually refresh it, if desired.

 

Click OK to close the Edit Parameters window, and if everything was done correctly, the data function should execute. If you get an error, walk back through the steps to make sure everything was setup correctly.

View the Pasted Values

You can now close the Data function properties window and test out the new functionality. While your testing things out, I recommend adding a data table visualization next to your text area like I did below. After adding the table, change the source data to the Output from the data function, which we’ve called outputTable. Copy a list of values from Excel and paste them into your Input Box. One key feature to keep in mind about this tool is that the fist value in the list will be the column name, so if you’re pasting in API values, just make sure that API (or whatever you want to name the column) is the first value pasted in, like this:

 

 

Marking and Filtering to Pasted Values

Now that data function is generating a new table with the pasted values, all we have left to do to is Mark or Filter to the data that we pasted in. Since these two sets of data are in separate data tables, we’ll have to create a Table Relation to tell Spotfire which columns are related between the 2 tables. To do this, click Edit > Data Table Properties. Then click the Relations tab, which will show all relations for the selected table (I have none yet).

 

As you can see, my Spotfire project only has one original table, Source_Well_Header, and the table generated by the Data Function, outputTable. To create a relation between two tables, click Manage Relations. Then click New, and select the two tables you want to relate. It doesn’t matter which one is Left or Right, just be sure to select outputTable in one of the dropdowns, and then select the table you want to relate it to. You’ll have to repeat this if you want to relate your outputTable with multiple tables in your project.

For the outputTable dropdown, there should only be one column available, and the name of the column will be the first line item from your input box. I’ve called mine API. Then select the column from the table you want to relate it to. The well header table I’m using is called Source_Well_Header, and the API numbers are in the Well_ID column, so I’ve selected this in the dropdown. If your pasting in Operator, Lease, Propnums, or some other type of data, select the appropriate column.

At this point, I’d also recommend selecting a Method to convert the data types. The data function tends to convert API numbers into real numbers, which can affect the relationship if the data type of the column in your related table is a String or some other data type. To make sure your data types are the same for the relationship, choose String for the Left and Right Methods.

 

Click OK to close the Edit Relation window, OK again to close the Manage Relation window, and now we can see that these two tables are related:

 

Adding a table relation lets you share Markings and Filters between tables, so now we can paste in a list of values into the input box and mark or filter to the pasted values. I’ve setup two visualizations in Spotfire to show the functionality. At the top is a Table Visualization with the Source_Well_Header data, and its configured to limit data to the marked rows in the outputTable. After pasting in a list of API numbers, I can mark them in one visualization (titled outputTable) and see the marked values in the Header table (titled Source_Well_Header). The map chart below is also tied to the Source_Well_Header, but it’s using the same Marking scheme as the outputTable, which means it will highlight the wells with the same API numbers that I select. There are many different ways that you can configure this to fit into your desired workflow, so just play with the different marking settings to make it work for you.

 

If you always want to filter to the values you pasted in (for example, it automatically filters the Header table to the values you paste in), you can link filters between the tables. Open the Filter Panel, navigate to the table you want to filter, and click the Filtering in related data tables button next to the table name. Then select Filtering in outputTable and choose Include Filtered Rows Only. Repeat this for any other tables that you’ve related and want to filter. In the example below, I’ve configured the filters in Source_Well_Header to automatically filter to the values in the outputTable.

 

Summary

That wraps up the steps on how to filter to a custom list that you paste into Spotfire! In this post I showed how to create a data function, link the inputs to a property control and the outputs to a new data table, and then find the values you’ve pasted in by either Marking or Filtering.

 

Download this Spotfire file for free here: https://exchange.ai/downloads/filter-pasted-values/. Even though it’s listed as “Subscriber Only”, you can login or setup a free Exchange.ai Portal account to download the template.

1 thought on “Filter to Pasted Values in Spotfire

  1. Pawel Reply

    Great article! I have one question as multiple input filter would be one of a couple of filters in my report. Is it possible to remove filtering once nothing is pasted in the filter?

Leave a Reply

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