Refresh data table

Description: Refresh an existing data table.
Assumptions: An optional 'myTable' data table type script parameter.

#Pick one of the following data table reference methods:
#myTable = Application.Document.Data.Tables['MyDataTable']        #Name method
#myTable = Application.Document.ActiveDataTableReference          #Active data table method
#myTable = Application.Document.Data.Tables.DefaultTableReference #Default data table method
#myTable as a data table type script parameter                    #Script parameter method

if(myTable.IsRefreshable and myTable.NeedsRefresh):
  myTable.Refresh()

Data table transformations

The following items are a collection of data table transformation code examples.

1. Pivot
   1. https://community.tibco.com/wiki/how-add-data-table-pivot-transformation-using-ironpython-tibco-spotfire
2. Unpivot
   1. See post by rgablejr: https://community.tibco.com/questions/how-create-unpivotted-table-iron-python
3. Insert columns
   1. https://community.tibco.com/wiki/how-insert-columns-existing-data-table-custom-column-match-tibco-spotfirer-using-ironpython
4. Insert rows
   1. https://community.tibco.com/wiki/how-append-new-rows-existing-data-table-tibco-spotfirer-using-ironpython-scripting
5. Change column names
   1. https://www.gmrinsight.com/managing-data/rename-data-table-column
6. Change data types
   1. See the "fixed" portion of william.campoli's 2nd post: https://community.tibco.com/questions/spotfire-change-datatype-column-datatable-ironpython
7. Data function
   1. https://www.cambridgesoft.com/support/EnterpriseSupport/KnowledgeBase/FAQ/details/Default.aspx?TechNote=3118
8. Exclude columns
   1. https://www.gmrinsight.com/managing-data/remove-data-table-column

Other useful examples

1. Add columns dynamically
   1. http://spotfired.blogspot.com/2014/04/add-columns-dynamically-to-data-table.html
2. Add in rows for missing data
   1. https://community.tibco.com/wiki/ironpython-script-add-rows-zero
   2. The conversation can be found here: https://community.tibco.com/questions/show-missing-data-0-and-line-drop-line-chart#node-686301

Connecting to data

Below are a collection of code examples for building data tables.

1. CSV file source
   1. https://community.tibco.com/wiki/ironpython-script-load-memory-csv-file-tibco-spotfire
2. Data table from current analysis
   1. https://community.tibco.com/wiki/how-add-new-data-table-based-existing-data-table-analysis-tibco-spotfirer-using-ironpython
3. Excel
   1. See post by Shandilya Peddi: https://community.tibco.com/questions/replacing-data-table-excel-worksheet-using-ironpython
4. ODBC
   1. http://spotfired.blogspot.com/2014/04/connect-to-db-programatically-via-odbc.html
5. Replace data table from a file
   1. Desktop client only: http://spotfired.blogspot.com/2014/05/replace-data-tables-from-file.html
6. Replace data table with Information Link
   1. http://easyspotfire.blogspot.com/2015/06/replace-datatable-with-information-link.html
7. Web service
   1. https://community.tibco.com/wiki/ironpython-script-access-data-web-service-using-httpwebrequest-and-parse-returned-json

All data table relations

Description: All data table relations.

for tableRelation in Document.Data.Relations:
  print "Left Table: " + tableRelation.LeftTable.Name
  print "Right Table: " + tableRelation.RightTable.Name
  print "Expression: " + tableRelation.Expression

Data table details

Description: Show all data table names, IDs, and row counts.

for myTable in Application.Document.Data.Tables:
  print "Table Name: " + myTable.Name
  print "Table ID: " + myTable.Id.ToString()
  print "Row Count: " + str(myTable.RowCount)

Rename data table

Description: Rename a data table.
Assumptions: An optional 'myTable' data table type script parameter.

#Pick one of the following data table reference methods:
#myTable = Application.Document.Data.Tables['MyDataTable']        #Name method
#myTable = Application.Document.ActiveDataTableReference          #Active data table method
#myTable = Application.Document.Data.Tables.DefaultTableReference #Default data table method
#myTable as a data table type script parameter                    #Script parameter method

print "Table Name: " + myTable.Name 
myTable.Name = "NewName"
print "Table Name: " + myTable.Name

Remove data table

Description: Remove a data table.
Assumptions: An optional 'myTable' data table type script parameter.
Warning: Backup Spotfire files before removing data tables.

#Pick one of the following data table reference methods:
#myTable = Application.Document.Data.Tables['MyDataTable']        #Name method
#myTable = Application.Document.ActiveDataTableReference          #Active data table method
#myTable = Application.Document.Data.Tables.DefaultTableReference #Default data table method
#myTable as a data table type script parameter                    #Script parameter method

#Application.Document.Data.Tables.Remove('DeleteMe') #Name method
Application.Document.Data.Tables.Remove(myTable)    #Data table method

Data table row values

Description: Get row values from two columns in a data table.
Assumptions: An optional 'myTable' data table type script parameter.

from Spotfire.Dxp.Data import DataValueCursor, IndexSet

#Pick one of the following data table reference methods:
#myTable = Application.Document.Data.Tables['MyDataTable']        #Name method
#myTable = Application.Document.ActiveDataTableReference          #Active data table method
#myTable = Application.Document.Data.Tables.DefaultTableReference #Default data table method
#myTable as a data table type script parameter                    #Script parameter method

rowCount = myTable.RowCount             #Get a count of existing rows
rowsToInclude = IndexSet(rowCount,True) #Include all existing rows

cursorDate = DataValueCursor.CreateFormatted(myTable.Columns["Date"])   #Prep to extract Date column
cursorValue = DataValueCursor.CreateFormatted(myTable.Columns["Value"]) #Prep to extract Value column

for row in myTable.GetRows(rowsToInclude,cursorDate,cursorValue):       #Loop through all existing rows
  myDate = cursorDate.CurrentValue   #Get Date
  myValue = cursorValue.CurrentValue #Get Value
  print myDate, myValue

Data table column and row counts

Description: Get a count of columns and rows in a data table.
Assumptions: An optional 'myTable' data table type script parameter.

#Pick one of the following data table reference methods:
#myTable = Application.Document.Data.Tables['MyDataTable']        #Name method
#myTable = Application.Document.ActiveDataTableReference          #Active data table method
#myTable = Application.Document.Data.Tables.DefaultTableReference #Default data table method
#myTable as a data table type script parameter                    #Script parameter method

rowCount = myTable.RowCount              #Get a count of rows
columnCount = myTable.Columns.Count      #Get a count of columns
print columnCount, rowCount

Rename data table column

Description: Rename a column in a data table. 
Assumptions: An optional 'myTable' data table type script parameter.

#Pick one of the following data table reference methods:
#myTable = Application.Document.Data.Tables['MyDataTable']        #Name method
#myTable = Application.Document.ActiveDataTableReference          #Active data table method
#myTable = Application.Document.Data.Tables.DefaultTableReference #Default data table method
#myTable as a data table type script parameter                    #Script parameter method

myColumn = myTable.Columns.Item["Date"]
myColumn.Name = "DateNew"

Remove data table column

Description: Remove a column from a data table.
Assumptions: An optional 'myTable' data table type script parameter.
Warning: Backup Spotfire files before removing columns.

#Pick one of the following data table reference methods:
#myTable = Application.Document.Data.Tables['MyDataTable']        #Name method
#myTable = Application.Document.ActiveDataTableReference          #Active data table method
#myTable = Application.Document.Data.Tables.DefaultTableReference #Default data table method
#myTable as a data table type script parameter                    #Script parameter method

#Remove by name method
myTable.Columns.Remove("NewColumn3")

#Remove by object
myColumn = myTable.Columns.Item["NewColumn2"] 
myTable.Columns.Remove(myColumn)

Data table column properties

Description: Display data table column properties.
Assumptions: An optional 'myTable' data table type script parameter.

#Pick one of the following data table reference methods:
#myTable = Application.Document.Data.Tables['MyDataTable']        #Name method
#myTable = Application.Document.ActiveDataTableReference          #Active data table method
#myTable = Application.Document.Data.Tables.DefaultTableReference #Default data table method
#myTable as a data table type script parameter                    #Script parameter method

for c in myTable.Columns:
  print "Column Name: " + c.Name
  print "IsValid: " + str(c.IsValid)
  print "IsVisible: " + str(c.Visible)
  print "DataType: " + str(c.DataType)
  print "Origin: " + c.Properties.GetProperty('Origin')
  print "Expression: " + c.Properties.Item['Expression']
  print "Calcualted Expression: " + c.Properties.Item['CalculatedExpression']
  print "Column Type: " + str(c.Properties.Item['ColumnType'])
  print "Data Type: " + str(c.Properties.Item['DataType'])
  print "Description: " + c.Properties.Item['Description']
  print "External Id: " + c.Properties.Item['ExternalId']
  print "External Name: " + c.Properties.Item['ExternalName']
  print "\r\n" #Print a blank line

Data table row counts for marking groups

Description: Count marked rows in a data table for all marking groups.
Assumptions: An optional 'myTable' data table type script parameter.

#Pick one of the following data table reference methods:
#myTable = Application.Document.Data.Tables['MyDataTable']        #Name method
#myTable = Application.Document.ActiveDataTableReference          #Active data table method
#myTable = Application.Document.Data.Tables.DefaultTableReference #Default data table method
#myTable as a data table type script parameter                    #Script parameter method

for marking in Document.Data.Markings:
  print "Marking Name: " + marking.Name
  print "Marking Color: " + marking.Color.Name, marking.Color.ToString()
  print "Table Name: " + myTable.Name 
  print "Marked Row Count: " + str(marking.GetSelection(myTable).IncludedRowCount)
  print "\r\n"

Filtered data table records

Description: Display filtered data table records.
Assumptions: An optional 'myTable' data table type script parameter.

#Pick one of the following data table reference methods:
#myTable = Application.Document.Data.Tables['MyDataTable']        #Name method
#myTable = Application.Document.ActiveDataTableReference          #Active data table method
#myTable = Application.Document.Data.Tables.DefaultTableReference #Default data table method
#myTable as a data table type script parameter                    #Script parameter method

filteredRows = Document.ActiveFilteringSelectionReference.GetSelection(myTable).AsIndexSet()

for row in filteredRows:
  for column in myTable.Columns:
    print column.RowValues.GetFormattedValue(row)

Display data table properties

Description: Display all data table properties.

from Spotfire.Dxp.Data import DataPropertyClass #Document = 0, Column = 1, Table = 2

for property in Document.Data.Properties.GetProperties(DataPropertyClass.Table):
  print "Property Name: " + property.Name
  print "Property Description: " + property.Description
  print "Property Value: " + str(property.Value)
  print "\r\n"