Spotfire IronPython script: get Cross Table data

flux picture flux · Mar 19, 2015 · Viewed 7.7k times · Source

How can I output the data object into text.

crossTable = markMe.As[CrossTablePlot]()
print crossTable.Data

Returns:

Spotfire.Dxp.Application.Visuals.VisualizationData object at 0x000000000000002C [Spotfire.Dxp.Application.Visuals.VisualizationData]

I've also tried:

for text in crossTable.Data:
    print text

which returns an error:

Microsoft.Scripting.ArgumentTypeException: iteration over non-sequence of type

How can I get the plot data so I can ultimately mark items in it?

https://docs.tibco.com/pub/doc_remote/spotfire/6.5.0/api/?topic=html/P_Spotfire_Dxp_Application_Visuals_Visualization_Data.htm

Answer

clesiemo3 picture clesiemo3 · Mar 20, 2015

Your issue is that you are grabbing the data table itself from your visualization which is not a collection of your records but rather a collection of columns which hold collections of row values.

The below should get you there. I used a data set where the column "test1" holds 6 rows with values 1 to 6 inclusive.

The overall flow of the code below is as follows:

  1. Get Data Table from Visual
  2. Setup Variables
  3. Loop through column of interest for row values
  4. Use some comparison to determine if the item should be marked.
  5. Mark all items that passed our test.

Code:

from Spotfire.Dxp.Application.Visuals import CrossTablePlot
from Spotfire.Dxp.Data import IndexSet
from Spotfire.Dxp.Data import RowSelection
from Spotfire.Dxp.Data import DataValueCursor
from Spotfire.Dxp.Data import DataSelection

##Get our Data Table from our graph. Data Tables hold marking relations. 
##Visuals just point to a marking set you specify 
##and interact with it in the UI based on their DataTable.
crossTable = markMe.As[CrossTablePlot]()
crossSource = crossTable.Data.DataTableReference

##Get a Row Count
rowCount = crossSource.RowCount

##Index Set of all our rows
allRows = IndexSet(rowCount,True)

##Empty Index Set to fill with our desired markings
rowsToMark = IndexSet(rowCount,False)

##Pick the column we're interested in examining for values.
##You can create multiple cursors to look at multiple columns.
##Specify the name of your column. Mine is called test1.
colCurs = DataValueCursor.CreateFormatted(crossSource.Columns["test1"])

##Optional: Loop through to determine average value
colTotal = 0
for row in crossSource.GetRows(allRows, colCurs):
    colTotal += int(colCurs.CurrentValue)
colAvg = colTotal/rowCount

##loop through our rows and add what we want to our index.
for row in crossSource.GetRows(allRows, colCurs):
    ##Get the index of our current row in the loop
    rowIndex = row.Index

    ##Compare values and if TRUE then we add this row to our index
    ##Instead of hard coding you can refer to a document property
    ##or any other source of data like the average of your column.
    ##Optional: Print our current value to debug
    #if int(colCurs.CurrentValue) > (2.5):
    if int(colCurs.CurrentValue) > colAvg:
        print colCurs.CurrentValue + " was added to the index! =]"
        rowsToMark.AddIndex(rowIndex)
    else:
        print colCurs.CurrentValue + " was not added to the index... =["

##Set our marking equal to our rowsToMark index
Document.ActiveMarkingSelectionReference.SetSelection(RowSelection(rowsToMark),crossSource)

Sources:

My own Spotfire Client and API knowledge

http://www.bearonspotfire.com/mark-rows-and-unmark-rows-using-scripts-in-spotfire

bearonspotfire is a nice resource for scripting as well. I took what he had done and put a bit of a twist on it for your application. He uses a drop down to mark items.

Edit for diablo8226's question:

flux (OP) and I were running on the same assumption that markMe is included as an input parameter of the visualization in question. You can include this as well by clicking the "Add..." button underneath your Script input window. You can use markMe or any name. Just make sure you select Visualization as the Type and select your visualization.

Alternatively you can input your data table itself and skip my code that grabs the data table source or call the table explicitly in code such as the below:

coll = Application.GetService[DataManager]().Tables   
crossSource = coll.Item["TABLE_NAME"]