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

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).

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

 

Final Notes

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!

5 thoughts on “Part 3 – Spotfire Properties – Column Properties

  1. Hi Julie,

    I was creating a report and in that the Content Type for one of the columns changed to “chemical/x-daylight-smiles”. My question is is it possible for the content type on change on its own. By default we keep the Content Type as blank and i remember never making any change in the Content Type. I would really appreciate your thoughts on this.

    Thanks.

    1. I just want you to know that before your comment, I didn’t know very much about ContentType. I started digging into it to answer your question. In doing so, I stumbled upon some information about using the ContentType property to render geometry columns in tables, which complements other work I have done with spatial data on maps in feature layers. If I hadn’t been looking up ContentType for you, I never would have made that connection. As a result, I wrote up this new blog post on using spatial data in Spotfire (creating information links, rendering, etc) — https://datashoptalk.com/spatial-data-information-links/. Take a look if you get a chance.

      1. Hi Julie,

        Thanks a lot for looking into this and sharing your findings. I read your post on Spatial Data and got to know a lot from that. Till date i have generally worked with Data Connections as we have Impala Database, but i tried my hands with Information Links, Spatial Data and Spotfire Map chart and your post really helped me.
        One thing in below comment… You have talked about how users don’t directly edit column properties from within menu. Is this just a convention that we follow. Because in my use case I had a data connection and thus could only edit column properties from the menu itself . I actually needed to delete the content-Type that was set to “chemical/x-daylight-smiles” and I did that from the Properties tab in Edit > Column Properties . Then I went to the table visualization and changed the Renderer for the affected column to Text. This is what worked for me.
        Thanks again for your really prompt reply…

  2. Here’s what I know…first, just to get it out of the way, users don’t directly edit column properties from within the menu. Rather, the application can update the columns (or column properties) you see in Column Properties dialog. For example, if you change a data type, the application updates the Column Type property for a given column.

    OR

    An administrator can attach column properties to the Column Element via the information designer that are then visible in Edit — Column Properties. For example, I create information links that use blob columns for feature layers in maps, usually sourced from a GIS data warehouse. To make those information links work, I have to add the mapchart.columntype property to the blog Column Element. After modifying the Column Element, the property is visible in Column Properties. In doing some testing to figure out the answer to your question, I was able to update the ContentType by adding this as a property to the Column Element in the information designer.

    Based on this little bit of knowledge I have, plus some testing, plus this information that I found in TIBCO help (copied below)…I don’t think that the application updated the ContentType. I would speculate that someone edited the Column Element properties. The name “chemical/x-daylight-smiles” follows the syntax described below. To test that, just look at the Column Element in the information designer. I have a couple screenshots I can send you if you email me at julie@bigmountainanalytics.com. I can’t attach them to the comment. I hope that helps.

    —-Each column may have a specified content type. Renderers use this property as input to know what to display. Use the form toplevel/subtype, for example, text/plain or image/jpg.

    For Geometry columns the content type should be set to application/x-wkb if you want to show the geometry information as images. —-

Leave a Comment

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