Introduction
The Spotfire® API allows users to set custom sort orders for columns in the data table.
Set Custom Sort Order
The below script reads a custom sort order from a column in Spotfire data table and applies the custom sort order to the column in the main data table. This is particularly useful when you want to order the bars in a bar chart by something other than a numerical value (or by size). It is also useful where the set of possible values for the custom sort order is not known during the authoring of a Spotfire file.
To further explain - imagine the main data table has many rows containing sales information for different types of fruit. The need is to sort the "Fruit" column (where "Fruit" contains the name of the fruit) in that table by the size of the fruit, but that's not possible as the size information is not present in that data table.
Alphabetically sorting fruits by their name does not work either! Apple, Grape, Pear, just isn't correct.
So - another data table contains the fruit and nominal diameter. This script will read the data from the secondary table to build a list (in order of diameter). It will then apply the order of that list to the Fruit column in the main data table.
As a secondary function, the script will also colour bars in a bar chart according to data stored about the fruit in the secondary data table.
Before
After
Script
# Copyright © 2017. TIBCO Software Inc. Licensed under TIBCO BSD-style license. # Andrew Berridge, TIBCO Software, 2015 from Spotfire.Dxp.Data import * from System.Collections.Generic import List from Spotfire.Dxp.Application.Visuals import * import System.Drawing.Color # Get a reference to a visual on the page specified def getVisual2(page, visualTitle): for vis in page.Visuals: if vis.Title == visualTitle: return vis return None #Function to return a Spotfire Data Table. Will return None if the data table does not exist #parameter: tableName - the name of the data table in the Spotfire document def getDataTable(tableName): try: return Document.Data.Tables[tableName] except: print ("Cannot find data table: " + tableName + ". Returning None") return None # A class represeting fruit - with name, diameter and colour class Fruit: def __init__(self, name, diameter, colour): self.name = name self.diameter = diameter self.colour = colour def __repr__(self): return repr((self.name, self.diameter, self.colour)) dt = getDataTable("Fruits") #Create cursors for the columns that will be iterated over fruitCursor = DataValueCursor.CreateFormatted(dt.Columns["Fruit"]) diameterCursor = DataValueCursor.CreateNumeric(dt.Columns["Diameter"]) colourCursor = DataValueCursor.CreateFormatted(dt.Columns["Colour"]) # Array to hold the fruit objects fruits = [] # Iterate over the rows in the Fruits data table for row in dt.GetRows(fruitCursor, diameterCursor, colourCursor): fruit = fruitCursor.CurrentValue diameter = diameterCursor.CurrentValue colour = colourCursor.CurrentValue print fruit + ": " + str(diameter) fruits.append(Fruit(fruit, diameter, colour)) # Sort the fruit by diameter sortedFruits = sorted(fruits, key=lambda fruit: fruit.diameter) print sortedFruits # Get the bar chart barchart = getVisual2(Document.ActivePageReference, "Number in Basket per Fruit").As[BarChart]() # Clear the color axis coloring barchart.ColorAxis.Coloring.Clear() # Create a .Net list to hold the fruit names in diameter order fruitList = List[str]() for fruit in sortedFruits: fruitList.Add(fruit.name) barchart.ColorAxis.Categorical.ColorMap[fruit.name] = System.Drawing.Color.FromName(fruit.colour); #Set the custom sort order dt.Columns["Fruit"].Properties.SetCustomSortOrder(fruitList)
License: TIBCO BSD-Style License
Recommended Comments
There are no comments to display.