Have you ever needed to apply manual inputs to a Well Header table in Spotfire and have those inputs apply to all the observations for the well in a Production table? Say you wanted to give a different note for each well, provide a weight to utilize in a calculated column, or provide a parameter in an economics table. To do so, first you have to figure out how to create the manual input in a header and apply that across the production table for each observation of the well, which we will show you how to do today.
For this post, we are going to assume you have a Well Header table. If you need a table with one unique identifier per well, check out this post. Once you are set with a header table, proceed.
First, we need a column for the user to input their desired values. Because we will be using this column in a subsequent transformation, we need to create this calculated column not through traditional column properties but through a transform. In pre-Spotfire X versions, go to Insert > Transformations > Calculate new column > Add. In Spotfire X, go to Data > Transform data > Calculate new column > Add. By creating a column this way, you can also use the column in joins, pivots, as key columns, etc.
Now, type the word null in the expression window and name the column as desired (we named ours User Roll Avg Input). We end up with a column with all nulls to use as our user input. Try double clicking one of the blank cells. You will probably get something like this:
Notice that you can replace the null values with whatever you want. However, until we assign a unique ID, you can only replace all occurrences in the column at once. Since our whole input column is null, this would give the same value to all wells. This behavior may be what you want, but if you want to apply different values to different wells, there are a couple of additional steps.
- In the window above, choose Select key columns and select your Well ID or Well Name column.
- Put your value in, select the This occurrence only circle, and click Apply.
Now, remember, we have only applied this input to our Well Header. We need to join this column to our production table. Click the Data canvas icon , then the plus symbol to the left of your table on the data diagram, then Add columns.
Choose Other and select your Well Header table from Linked copy to data table analysis. Click SETTINGS FOR ADDED COLUMNS and make sure your identifier column (Well ID or Well Header) is matched. Do not match any other column like Date or else the join will only apply to the rows that match the header table exactly.
Next, choose your input column to join to the Production table.
Hit OK twice and your input column will be joined to your header table:
You can see above that one input is applied to all instances of well name 1 and the same goes for well name 2. The fantastic part about this is that when you update the inputs in the Well Header, the values in the Production table will update automatically,
This trick can help us utilize our Well Header as an input table for our production table. Which assumes you have both tables. If all of your data is in one table and you want to learn how to create a virtual header table to do the same thing, check out this post.
Jason is a Data Scientist at Ruths.ai with a master’s degree in Predictive Analytics and Data Science from Northwestern University. He has experience with a multitude of machine learning techniques such as Random Forest, Neural Nets, and Support Vector Machines. With a previous Master’s in Creative Writing, Jason is a fervent believer in the Oxford comma.