Useful IronPython Scripts from Spotfire's COVID19 Dashboard

Big Refresh

This script refreshes specific Data Tables and Data Functions then plays 3 beeps at the end to alert the analyst

from System.Collections.Generic import List, Dictionary 
from Spotfire.Dxp.Data import DataTable 
from System.Collections import ArrayList
import winsound

tables = ArrayList()

for dt in Document.Data.Tables:
	if dt.Name.StartsWith("FINAL"):
		tables.Add(dt)

for dt in tables:
	print "Will Refresh: " + dt.Name

#UNCOMMENT me to refresh!
Document.Data.Tables.ReloadAllData(tables)


#Now execute the data functions:
dataFunctions = []
for df in Document.Data.DataFunctions:
	if df.Name.Contains("Get Re") or df.Name.Contains("Virulence"):
		dataFunctions.append(df)

while dataFunctions:
	df = dataFunctions.pop()
	print df.Name
	df.ExecuteSynchronously()

for i in range(3):
	winsound.PlaySound("*", winsound.SND_ALIAS)


Document.Properties["viewreport"] = ""
Document.Properties["setpage"] = ""

Tablecloth

Need to swap a table but don't want to break your references to every chart, data function, etc? Use the "Tablecloth" script to rip away one table and replace it with another table while leaving all your references in place. Check out the section on "Function Definitions" to specify how columns should be handled.

from Spotfire.Dxp.Data import *
from Spotfire.Dxp.Framework.Library import *
from Spotfire.Dxp.Framework.ApplicationModel import *
from Spotfire.Dxp.Data.Import import *
import clr
from System import Array
lm = Application.GetService[LibraryManager]()
li = clr.Reference[LibraryItem]()

#set params from document properties
ReplaceDT = Document.Properties["DevTools.ReplaceDT"]
SourceDT = Document.Properties["DevTools.SourceDT"]
CalcColBool = Document.Properties["DevTools.CalcCols"]
ResultColBool = Document.Properties["DevTools.ResultCols"]
print ReplaceDT
print SourceDT

#function definition
def tableCloth(dataTableName, pathToSBDF):
    print lm.TryGetItem(pathToSBDF, LibraryItemType.SbdfDataFile, li, Array.CreateInstance(LibraryItemRetrievalOption,0))
    ds = SbdfLibraryDataSource(li.Value)
    dt = Document.Data.Tables[dataTableName]
    removeCalculatedColumns = CalcColBool # Set this to true if you are replacing calculated columns with imported columns
    removeResultColumns = ResultColBool # Set this to false to leave result columns alone
    columnsToLeaveAlone = "Put Column Names here"; # Add any columns here that you want to exclude from ripping out - i.e. those that you are not replacing

    # Remove result columns and/or calculated columns - these columns should be present in the underlying SBDF and the names must
    # match the existing result/calculated column names
    colsToRemove = []
    for c in dt.Columns:
        print c.Name
        print c.Properties.ColumnType	
        #if not columnsToLeaveAlone.contains(c.Name):
        if c.Properties.ColumnType == DataColumnType.Result and removeResultColumns:
            colsToRemove.append(c)
        if c.Properties.ColumnType == DataColumnType.Calculated and removeCalculatedColumns:
            colsToRemove.append(c)              

    for c in colsToRemove:
        dt.Columns.Remove(c)

    dt.ReplaceData(ds)

tableCloth(ReplaceDT, SourceDT)

Dev / Prod Source Swap

Need to develop your DXP with a different data source that's in production? Use this to swap SBDF sources from the library without breaking any column references and to execute on all desired tables on one click. Note that "FINAL -" is used as a prefix to some data tables to easily flag them for replacement.

#-----------------------------
# IMPORT NAMESPACES
#-----------------------------
from Spotfire.Dxp.Data import *
from Spotfire.Dxp.Framework.Library import *
from Spotfire.Dxp.Framework.ApplicationModel import *
from Spotfire.Dxp.Data.Import import *
import clr
from System import Array

#-----------------------------
# FUNCTION DEFINITIONS
#-----------------------------

# Tablecloth: replace data table from sbdf library
lm = Application.GetService[LibraryManager]()
li = clr.Reference[LibraryItem]()

def tableCloth(dataTableName, pathToSBDF):
    print lm.TryGetItem(pathToSBDF, LibraryItemType.SbdfDataFile, li, Array.CreateInstance(LibraryItemRetrievalOption,0))
    ds = SbdfLibraryDataSource(li.Value)
    dt = Document.Data.Tables[dataTableName]
    removeCalculatedColumns = False # Set this to True to replace calculated columns with imported columns
    removeResultColumns = False # Set this to True to replace result columns (from DFs)
    columnsToLeaveAlone = "Put Column Names here"; # Add any columns here that you want to exclude from ripping out - i.e. those that you are not replacing

    # Remove result columns and/or calculated columns - these columns should be present in the underlying SBDF and the names must
    # match the existing result/calculated column names
    colsToRemove = []
    for c in dt.Columns:
        #print c.Name
        #print c.Properties.ColumnType
        #if not columnsToLeaveAlone.contains(c.Name):
        if c.Properties.ColumnType == DataColumnType.Result and removeResultColumns:
            colsToRemove.append(c)
        if c.Properties.ColumnType == DataColumnType.Calculated and removeCalculatedColumns:
            colsToRemove.append(c)              

    for c in colsToRemove:
        dt.Columns.Remove(c)

    dt.ReplaceData(ds)


def TraverseSourceView(op):
    if op.DisplayName != "Transformations" and op.DisplayName != "Added columns":
        return op.DisplayName
    else:
        for input in op.Inputs:
            #print input
            try:
                return input.GetDataFlow().DataSource.Name
            except:
                pass
            return TraverseSourceView(input)
            

#-----------------------------
# EXECUTE ROUTINES
#-----------------------------            
SetPath = Document.Properties['DevTools.SetPath']

if SetPath == "PROD":
     pathSBDF = "/Data Science Team/COVID19/Production Data/" #PROD Folder
else:
     pathSBDF = "/Data Science Team/Testing/COVID19 Main Dashboard/Automation Testing/Production Data/" #DEV Folder
     
print("New SBDF Paths: " + pathSBDF)

for dt in Document.Data.Tables:
    if dt.Name.startswith("FINAL"):
        print "Table Name: " + dt.Name
        
        sourceView = dt.GenerateSourceView();
        source = TraverseSourceView(sourceView.LastOperation)
        #print "Final source: " + str(source)
        
        sourceSBDF = pathSBDF + str(source)
        #print "Full Path: " + sourceSBDF
        
        tableCloth(dt.Name, sourceSBDF)

Document.Properties['DevTools.CurrentPath'] = SetPath + " Library Folders"

print("Success")