Add columns dynamically to a Data Table

In this example, we use a Property Control to add columns to a Table Plot Visualization. For this exercise, the Underlying data table must have 6 columns called 'col1,col2,..,col6'





Ingredients:
 * Data Table Visualization
 * Text Area
 *  Property Control

Preparation:

1) Add a Data Table Visualization and a Text Area. Edit the Text Area and add a List box (multiple select) control. Create a new property called 'groups' and add Fixed values for each item. The display name can be something like 'Group 1' and the value, a comma separated list of columns i.e. 'col2,col3,col4'.

2) Click on the Script button and add 1 tbs of the following script. If there is no script button, you would need to add a button to trigger the script:


#This script takes myDataTable Visualization as a script parameter

#It adds columns to a Data Table based on a property. The property has a comma separated list of columns for the data table to show.
from Spotfire.Dxp.Application.Visuals import TablePlot, VisualContent
from Spotfire.Dxp.Data import DataPropertyClass

#get underlying data table
dt=myDataTable.As[VisualContent]()
cols = dt.Data.DataTableReference.Columns

#remove all columns
dt.TableColumns.Clear()

#get document property
selection = Document.Data.Properties.GetProperty(DataPropertyClass.Document, "groups").Value

#parse columns from selection
for property in selection:
   for col in property.split(","):
      #add columns from document property (ignore duplicates)
      try:
         dt.TableColumns.Add(cols[str(col)])
      except:
         print col + " is already in table"
  1. Add a Visualization Script parameter called 'myDataTable'

6 comments:

John said...

Thank you for your blog site and this innovative soluitions. One of hte struggles I have when creating scripts is determining the classes and methods I need to use to perform the desired task. What references do you pull from to determine the proper classes, methods, and properties?

Leviaguirre said...

I use the API http://stn.spotfire.com/dxp

Ronnie Arispe said...

I'm having some difficulty. The output returns "L is already in table", "E is already in table", "A is already in table", etc., spelling out each column name.

Jose Leviaguirre said...

Ronnie,

The code is hitting an exception and telling you about it in the console. This is OK. Spotfire cannot add the same column twice on data table visualizations so it is a way to prevent the code from crashing

Tali H said...

Hi, first off, thanks so much for posting the scripts. Your blogspot has been invaluable during my project. I've modified your script slightly so that you can have several multiple select list controls and one action script button - all in one text box. This makes it easier for the user to find the column they want. Thing is - if one of the multiple select list controls doesn't have a selection, it returns this error:

Could not perform action 'Show Selected Columns'.
iteration over non-sequence of type
at IronPython.Runtime.Operations.PythonOps.GetEnumeratorForIteration(CodeContext context, Object enumerable)

I think it's possible to put a few lines in the script to tell it to ignore anything boxes that don't have anything selected, but I'm not sure how to do this.

Jose Leviaguirre said...

Hello Tali,

Thanks for your feedback. I am glad this blog is useful for you and many. The goal of this blog is to provide developers with sample code snippets for their projects, hence they are not perfect but hopefully good enough to get you started. What you did by changing the script is exactly what I expect from users like you.

One way to handle errors is by using try-except clause.

try:
#code that breaks
a+1
except Exception,e:
#code that should never break if error happens
print "There was an error. The error was:\"" + str(e) + "\""