Problem:
I want to create a new data table from an existing cross table visualization.
Resolution:
This is possible using IronPython scripting. Read the instructions below.
#This script has been tested in Spotfire 14.0 LTS and beyond.
1. Create a cross table from data table A.
2. Rename the visualization title to My cross table.
2. Insert a text area into Spotfire analysis. Add an Action Control button that will execute the following script.
|
import System
from Spotfire.Dxp.Data import DataType,DataTableSaveSettings
from Spotfire.Dxp.Data.Import import TextFileDataSource,TextDataReaderSettings
from System.IO import Path,StreamWriter,StringReader,StreamWriter,MemoryStream,SeekOrigin
from Spotfire.Dxp.Application.Visuals import VisualContent
from Spotfire.Dxp.Application.Visuals import CrossTablePlot
from Spotfire.Dxp.Data import DataValueCursor,RowSelection,IndexSet
def getVisual(visualTitle):
for page in Document.Pages:
for vis in page.Visuals:
if vis.Title == visualTitle:
return vis.As[VisualContent]()
vis = getVisual("My cross table")
stream=MemoryStream()
writer=StreamWriter(stream)
vis.ExportText(writer)
stream.Seek(0,SeekOrigin.Begin)
readerSettings=TextDataReaderSettings()
readerSettings.Separator="\t"
readerSettings.AddColumnNameRow(0)
textDataSource =TextFileDataSource(stream,readerSettings)
# add the data into a Data Table in Spotfire
if Document.Data.Tables.Contains("DataFromCrossTable"):
Document.Data.Tables["DataFromCrossTable"].ReplaceData(textDataSource)
else:
newTable = Document.Data.Tables.Add("DataFromCrossTable", textDataSource)
tableSettings = DataTableSaveSettings (newTable, False, False)
Document.Data.SaveSettings.DataTableSettings.Add(tableSettings)
myTable = Document.Data.Tables["DataFromCrossTable"]
n = 0
for col in myTable.Columns:
n = n + 1
if n == 1:
MyCol = col.Name
LastRow = myTable.RowCount
rowsToRemove=IndexSet(myTable.RowCount,False)
# Reference to the Column of the Table
dataValuesCursor=DataValueCursor.CreateFormatted(myTable.Columns[MyCol])
i = 0
for row in myTable.GetRows(dataValuesCursor):
i = i + 1
if i == LastRow:
rowsToRemove.AddIndex(row.Index)
myTable.RemoveRows(RowSelection(rowsToRemove))
|
3. Running the script will create a new data table DataFromCrossTable.
4. Re-running the script will update the data table to the current cross table state.