Part 3 – Spotfire Properties – Column Properties

This is the third part of a series on Spotfire Properties.  In previous posts, I discussed Document Properties and Data Table Properties.  This week we’ll take a look at Column Properties.

Column Properties

As the name indicates, the Column Properties dialog deals with properties for columns in a data table.  Now, that explanation seems clear and simple, but when making the switch from Excel to Spotfire, there are a few things that get lost in translation.  That’s okay.  We’ll work thru it in this post.

Column Properties contains 5 tabs.  No matter which tab you click on, the data table drop down and the list of column names will always be visible.  You will also always see the Insert and Delete buttons.  The Insert button allows you to insert a calculated column, a binned column, or a hierarchy column.  This might be preferable to using the Insert menu because you can insert more than one calculated column at a time without exiting the dialog.

Additional features of this main dialog include:

  • Type to search — This is almost always faster than scrolling, and you can use wildcards (“*”).  For example, if I want to search for any column of data with the word date, I would search “*date*”.
  • Click to sort — Users may also click on the column headers to sort.  I frequently use this when I create new information links, and I need to check for Currency data types.  I click the Column Type header and can quickly see if I have any Currency columns.
  • Metadata — There is a ton of information about each column.  See below for more detail.

Column Properties Header

 

Meta Data

As you can see from the screenshot, there is a ton of information about each column.  This is too much to cover in one post, so I’ll just go over the most important information.

  • Column Type — There are 9 column types — None, Imported, Calculated, Binned, Frozen, Result, Tags, Mask, and Hierarchy.  I’ve never seen “None” listed as a column type in any of my projects, but the Help menu says it’s an option.  It is important to note that if you create a calculated column with a transformation, the type reflects “Imported”, not “Calculated”.
  • Data Type – Self Explanatory
  • External Name — Users can and do change column names in the General tab.  The ExternalName shows the column name from the source.  I’ve found this to be very handy from time to time when I lose track of changes or am working on someone else’s project.
  • IsValid — Each column has a True or False value, but is the value is only relevant for calculated columns.  If the value is True, the column expression is valid.  If the value is False, the column expression is not valid.  False is bad.  That means the calculation is not working.  Mostly the user deleted a column used in the calculation.  Excel simply returns a #NA, but Spotfire returns a NULL, and the calculation becomes invalid.  These types of errors are hard to spot unless you know to review IsValid.
  • Origin — This metadata specifies the data table the column originates from, which is very important because this information is not provided elsewhere.  If you review Data Table Properties, Source Information, the dialog will list columns ignored, not columns added.  This isn’t terribly helpful.  However, you can easily insert column by reviewing the Origin column.

Next, let’s look at the General tab.

Column Properties Metadata

General

The general tab is used for two things.

  1. Changing column names
  2. Updating calculated, binned, or hierarchy columns

You may also Freeze columns here, but I have yet to encounter a scenario where I want to freeze a column.  Freezing columns locks the column and disallows editing. The Help menu says this function was created for scenarios where “you want to save the result from a calculation to prevent it from being overwritten or for performance reasons”.  However, freezing columns also embeds the table, which I rarely want.

Column Properties General

 

Formatting

The Formatting tab is self-explanatory, but I want to point out a few things.

  • Formatting columns of data are faster than formatting each and every visualization.
  • Spotfire has a short number format that takes up very little space on visualizations.  It’s handy, and I use it a lot.
  • Users may select many columns of data (of the same data type) and format them at the same time.
  • Users can apply formatting from one column to other columns so the same steps aren’t repeated unnecessarily.

Column Properties Formatting

Properties

The properties dialog lists all Column Properties in a DXP and then notes whether a particular column is part of that property.  Go to the next screenshot for an example.

Column Properties Properties1

In this example, I have created a drop down property control called MyDropDown.  It has 4 columns in it.  Note, the drop down is a Document Property.  By default, Spotfire will place all columns from a data table in the drop down unless you create a Column Property to limit the columns.  In this case, I created a Column Property called MyDropDownOptions restricting the drop down to only four columns.  Review the previous screenshot to see MyDropDownOptions listed in the Properties tab.  The value is set to True for c.Total BOE.  It would be False for Well Name, Prod Date, etc.
Column Properties Properties2

Geocoding

The geocoding dialog allows the user to specify that a column contains geographic information.  That information may be used for positioning data on a map.  This post is already quite long, so I won’t to get into geocoding details.

Column Properties Geocoding

Sort Order

When placing a categorical column of data on a visualization, such as a bar chart, the data sorts itself in the “Standard sort order”.  However, users often want to change this order, and that is possible here in Column Properties using the “Custom sort order”.

Column Properties Sort Order

 

And that wraps up Column Properties!  Next, I’ll discuss Data Function Properties.

Part 2 – Spotfire Properties – Data Table Properties

This is the second part of a series on Spotfire Properties.  In the previous post, I talked about Document Properties.  This week we’ll take a look at Data Table Properties.

Data Table Properties

As the name indicates, Data Table Properties controls everything related to data tables.  It’s organized into 6 tabs. No matter which tab you click on, the data table names and the four buttons on the right-hand side of the screen will always be visible.  You may notice each of the data tables has a square of color next to the name.  This is a visual indicator of whether or not tables are related.  If the tables are related, they will be the same color.  If the tables are not related, they will have different colors.

  • Rename — Allows the user to rename the table
  • Refresh Data — Click the button and then choose Refresh Data with or without prompt.  This button was CRITICAL before TIBCO added the ability to edit transformations from the Data Panel.  In the past, users had to use Refresh Data with Prompt to step through transformations, although this is no longer the case.
  • Delete — Allows you to delete tables.
  • Set as Default — When new visualizations are added, they are connected to the default table.  Change this to the table you use most frequently.

Data Table Properties General

The General tab has most of what users are looking for.

  • Store data — This radio button allows you to toggle between embedded and linked to the source.  Spotfire defaults to linked.  As long as it is linked, the DXP will ping the data source each time the file opens.
  • Key columns for linked data — This setting applies to tags and replacing specific values.  If you are new to Spotfire, check out this post on tags, which references this dialog.  Replacing specific values was a new function added in version 7.9.  Users may now double-click on a value in a table and replace that individual value if keys are set up in Data Table Properties.
  • Type of data — This box will appear gray and unavailable unless you are importing data On Demand.  If loading On Demand, this is where you can change On Demand settings.
  • Filters — By default, Spotfire will create a filter for each column of data in the filter panel.  You can change this setting by setting the radio button to ‘Manage manually’.  I talk about manually managing filters in this post.  Spotfire also defaults to caching columns automatically.  This setting can be changed in Data Table Properties or in Administrative Settings.  You can check out this post for more information on caching calculated columns.

Data Table Properties Source Information

Next, the Source Information tab displays the analysis build steps.  Before the Data Panel Source View, this was critical.  You may still need Source Information for more detailed information outside the Source View.  If I need to rebuild an analysis, I copy and paste this into Microsoft Word for reference.

Data Table Properties Source Info

Data Table Properties Relations

Relations facilitate marking and filtering across different data tables in Spotfire. If this case, I have two tables — Well Location Data and Well Producing Depth Data.  I have a common column between them that I want to filter on, so I create a relation to do that.  Click the Manage Relations button to see the relations.  This screen will be blank unless the user has created relations.

Data Table Properties Relations

Data Table Properties Column Matches

Column Matches are what allow users to place data from different tables on the same visualization.  Spotfire creates them for you when you have the same column (name and data type) in more than one data table.  For example, if I have a column called Well Name where the data type is a string in both tables, Spotfire creates the match.  In this particular case, my two tables didn’t have columns that matched the name and data type, but I created a column match manually on ComplName and Well_ID.

Data Table Properties Column Matchs

 

Data Table Properties Properties

Data Table properties are usually created in Information links and appear in the Properties tab.  Users may also create new properties here.

Data Table Properties Properties

 

Data Table Properties Scheduled Updates

Lastly, Scheduled Updates primarily impact analysis files used by the Web Player, as described in this blog post.  However, they can also assist in optimizing desktop files as well, as described in this blog post.  In this dialog, TIBCO provides a link to your server where you can set up scheduled updates for the DXP.  It also lists all source data tables contributing to the data table you have already selected above.  If you click one of the check-boxes, Spotfire excludes the table from the scheduled update and will reload when a new user opens the analysis.

 

Now, you know everything there is to know about Data Table Properties!

 

RStudio Error – “ERROR r error 4”

A few weeks ago, I wrote a post on TERR compatible versions of R and RStudio. At that time, I realized I needed to update my RStudio and R versions. In doing that, I ran into an RStudio error when launching my newly updated RStudio.  Since I ran into this problem, I am sure other users will too.  
RStudio Error
 
This error occurs when RStudio can’t find the installation of R. Fixing it is super simple. Simply hold down the Ctrl key when launching R and a popup will appear that allows you to specify the R installation location.
Fixing Error
Now, I’m unsure what order of operations I followed and/or if the order of operations caused my problem, but I would recommend the following…
  1. Uninstall R
  2. Uninstall RStudio
  3. Install new R
  4. Install new RStudio

Hopefully, your installation goes easier than mine did.

TERR Scripts to Read/Write to MS Access

Ruths.ai recently published a free template on the Ruths.ai Exchange that reads and writes data from/to MS Access.  Under the covers, you’ll find two property controls and two data functions working with the RODBC package.  Now, we know that templates are good, but being able to replicate the work is better.  Users want to be able to recreate that functionality in their own files, which is why I am writing this post to explain the code and how everything fits together so you can recreate this functionality in your own DXP files.  Before reading any farther, use this link to download a copy of the template and familiarize yourself with how it works.

Read More