Problem:
I've created a copy of a data table and then added a pivot transformation to summarize sales result.
I want to change the filters in my original table and have those changes be reflected in my pivot table.
Resolution:
You can create a copy of a table AND add a pivot transformation using IronPython script.
This way, the resulting pivot table will reflect the change in filter of your original data table.
See instructions below.
1. Using your original data table, create a cross table visualization. The axis setting in this visualization will be used to create a pivot table.
2. In a text area, add an action control button which runs the following IronPython script. Change the text in red to match the title of the cross table visualization.
|
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]()
#Modify the line below to specify the title of the cross table visualization
vis = getVisual("Sales summary")
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)
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
#Statement to remove or keep rows.
if i == LastRow:
rowsToRemove.AddIndex(row.Index)
myTable.RemoveRows(RowSelection(rowsToRemove))
|
3. Change the filter of the original data table.
4. Click on the action control button to run IronPython script.
5. A new data table DataFromCrossTable will be created based on the cross table axis setting and the filter changes in the original data table.