Setting display values based on other columns

Sometimes, you might want to show other values than the ones that actually define an axis as the display values. Display values can easily be configured in the expanded Data in analysis flyout.

For example, you might have data for customer IDs in an external data table (e.g., streaming data), together with lots of other information, but the mapping of the IDs to actual customer names is included in a smaller, imported data table. When one of the data tables is in-database it is not possible to add columns directly to join the two tables. Then, you can choose to show the data based on the external IDs in a visualization, but to change the display values to show the customer names from the imported data table as the axis labels instead. (Data and display values alike can come from either in-memory or in-db data tables when using display values.)

Display values are configured in the expanded Data in analysis flyout. Only string columns or expressions are allowed as display values.

When defining display values and there is a single matching string column available in the target data table, you can choose to show just the value from the other column, or a combination of the old value and the display value from the other column:

It is possible to specify similar configurations even if the shortcut options are unavailable. Do it by defining a custom expression on the display value column (such as, Concatenate([Employees].[Employee ID],": ",[Employees].[Name])). Note that expressions that mix columns from multiple data tables are not supported.

Note: Currently, only columns classified as identifiers or categories can have display values. If you select display values for another type of column then it will automatically be re-categorized as 'Categories'. This makes sure that expressions using this column expect a categorical expression instead of a continuous one. You cannot use display values with cube data and it is not possible to use images or geometry data as display values.

To use column values from a different data table, there must be a column match between the two data tables. This is often created automatically (e.g., if the name and data type of two ID columns in the data tables are the same) but it is also possible to configure specific column matches using Spotfire Analyst. See Multiple Data Tables in One Visualization in the Spotfire Analyst help for general information about matching data tables.

Note: When changing the display values for a column, the display values will be shown on all places where they make sense to show up, for example, as axis labels on categorical axes, in tooltips in visualizations, or in check box-, radio button-, item- or list box filters. Range filters and other continuous applications will always show the original values. Searching in text filters is always performed against the original data, so search for a display value will not result in any hits, whereas searching in list box filters will show hits for both display values and original values.

Procedure

  1. On the authoring bar, click Data in analysis , and click on the column you want to change the display values for.
  2. In the expanded flyout, make sure that Details on selected column is selected.
  3. In the lower part of the expanded flyout, click the column selector under Display values.
    If there is more than one data table in the analysis, and a column match is available, you will see a drop-down list at the top of the expanded column selector that lets you choose a data table. If you have a single data table in the analysis you can only choose columns from that data table. See Choosing the data to analyze and Choosing how to load the data for help on how to add another data table to the analysis.
  4. In the upper part of the column selector, choose the data table from which to pick the values for the display values.
    If you cannot see any columns in the list, you might not have a column match defined. See Column Matching in the Spotfire Analyst help for more information.
  5. Click on the column to provide the display values.
    You can also define a custom expression for the display values. For example, Concatenate([Employees].[Firstname], " ",[Employees].[Last name]). See below for more details.
    Visualizations and applicable filters are updated to show the display values instead of the original values.

Example

Tip: To exclude an axis in a single visualization from using the display values and instead use the original values in the column, you can specify a custom expression on that axis. For example: Integer([Employee ID]) as [Employee ID]

In this example, you have two different data tables in the analysis; the first one contains the salaries for a number of employees, identified using the employee number:

The second data table contains a mapping of the employee number to the first name and last name of the employees:

When showing the raw data from the salaries data table in a bar chart you would just see a bunch of bars with numbers on them, but by setting the display value for the employee number column to be the concatenated first name and last name of the mapping table (using the custom expression Concatenate([Employees].[First name]," ",[Employees].[Last name])) you will see which employee has which salary directly:

The display name for the Employee ID column in the Salaries data table has been specified in the expanded Data in analysis flyout:

See also Sorting columns with display values.