TIBCO designed Spotfire to bring data into the application, wrangle it into a useable form, and display visualizations for analysis. It’s not really designed to modify source data, but that doesn’t mean that people don’t want to or that you can’t. In fact, it’s easy to modify individual values in Spotfire if you know where to look, just like clicking on a cell in Excel. Read on for more information.
First, users may make 2 types of changes to values from 3 locations.
- Users may change a single cell or a single value.
- Users may change all occurrances of a value within a single column of data.
With a little prework, these changes can be made…..
- From a data table visualization
- Via the details on demand panel
- From the Data in Analysis menu
Before explaining the prework, I want to demonstrate what happens without the prework. Look at this example of the menu presented when double-clicking on a cell in a data table visualization.
Double-clicking pulls up the Replace value with menu. Notice, one option is grayed out. Without prework, I can change all instances of “Buttercup 2744WA” to something else, but I can’t change just this single cell.
Next, the same menu is also available in the Data in analysis menu after selecting a column of data and double-clicking on a value. Again, one option is grayed out. So, what prework needs to happen to enable this menu option?
Specify Key Columns
To enable full functionality, you must specify key columns via File – Data Table Properties. Specifying key columns tells Spotfire what makes a record unique so that it can record changes in individual records. From the Data Table Properties menu, click Edit.
The next dialogue will default to Recommended columns in the Limit available columns to: drop-down. You may not see the column you are looking for. To see more columns, set the drop-down to All columns. Spotfire makes recommendations based on the data type, but it can be wrong.
Next, select one or more columns of data to uniquely identify all rows. Move the column from left to right and click OK.
If the column selected doesn’t truly identify unique rows (i.e. there is more than one row for each key column value), this message will appear.
With the prework complete, return to the data table or other location. Now, all menu options appear. You may change either a single value or all occurrences of a value.
Behind the Scenes
Before wrapping up, I want to note that behind the scenes, Spotfire creates a Replace Value transformation. If I change Buttercup 2744WA to Well 1 and then go to my data canvas, the transformation step appears. That’s how it works!
Other Sweet Spotfire Content
If you enjoyed this post, share it on social media and check out some of my other work or my home page.