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.
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.
The general tab is used for two things.
- Changing column names
- 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.
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.
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.
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 can be added through this menu or also through the Information Designer. When using the Information Designer, the column property is added directly to the Column Element (in the case of information links).
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.
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”.
Before I wrap up Column Properties, I also want to note that you can create Column Properties for individual column elements in the Information Designer. That is a broad subject, and I’ve only done a few of these, so I’m not going to dive into it here. However, this blog post on creating information links for spatial data provides two examples of adding column properties to the column element. I would really like to learn what else is possible in this space.
And that wraps up Column Properties! Next, I’ll discuss Data Function Properties.
Guest Spotfire blogger residing in Whitefish, MT. Working for SM Energy’s Advanced Analytics and Emerging Technology team!