Jump to content
We've recently updated our Privacy Statement, available here. ×
  • IronPython script to Calculate Cross Table Sums


    This script allows users to calculate the grand total of the visible rows in a cross table. The calculated sums are presented in a new Spotfire data table. This is useful when you have used complex custom expressions in a cross table and the grand totals are not as expected.

    Introduction

    This script allows users to calculate the grand total of the visible rows in a cross table. The calculated sums are presented in a new Spotfire data table. This is useful when you have used complex custom expressions in a cross table and the grand totals are not as expected.

    Calculate Cross Table Sums

    The below script writes a cross table to memory in a tab-delimited format, reads it back in and calculated the sum for each of the columns. Finally, it outputs the sum to a new data table. 

    # Copyright © 2017. TIBCO Software Inc.  Licensed under TIBCO BSD-style license.
    
    # Calculate totals for CrossTable
    #	Andrew Berridge, TIBCO Software, October 2014
    #
    # Parameters:
    #	crossTable - the cross table for which to calculate the total
    #	totalsDataTableName - the name of the data table to save the totals in
    
    from Spotfire.Dxp.Application.Visuals import VisualContent
    import System.Drawing.Bitmap
    from System.IO import StringReader, StreamReader, StreamWriter, MemoryStream, SeekOrigin
    from System.Collections.Generic import Dictionary
    from Spotfire.Dxp.Data.Import import TextFileDataSource, TextDataReaderSettings
    from Spotfire.Dxp.Data import DataType, DataTableSaveSettings
    
    
    stream = MemoryStream()
    writer = StreamWriter(stream)
    crossTable.As[VisualContent]().ExportText(writer)
    
    writer.Flush()
    stream.Seek(0, SeekOrigin.Begin)
    reader = StreamReader(stream)
    
    totalsStream = MemoryStream()
    totalsWriter = StreamWriter(totalsStream)
    
    # set up the text data reader
    readerSettings = TextDataReaderSettings()
    readerSettings.Separator = "t"
    readerSettings.AddColumnNameRow(0)
    
    line = ""
    bFirstLine = True
    values = Dictionary[int, float]()
    while (True):
    	line = reader.ReadLine()
    	if line == None: break
    	print line
    	lineToWrite = ""
    	intCol = 0
    	for value in line.Split("t"):
    		if bFirstLine:
    			if value.Contains("Total"):
    				#Store 0 and the column index in the dictionary
    				values.Add(intCol, 0)
    				readerSettings.SetDataType(intCol, DataType.Real)
    				if lineToWrite != "": lineToWrite += "t"
    				lineToWrite += value
    			pass
    		else:
    			if values.ContainsKey(intCol):
    				print "value " + str(value)
    				values[intCol] += float(value)
    			
    		intCol += 1
    
    	if bFirstLine:
    		totalsWriter.Write(lineToWrite + "rn")	
    	bFirstLine = False
    
    bFirstTimeThrough = True
    lineToWrite = ""
    for value in values.Keys:
    	if not bFirstTimeThrough:
    		lineToWrite += "t"
    	print value
    	lineToWrite += str(values[value])
    	bFirstTimeThrough = False
    
    print lineToWrite
    totalsWriter.Write(lineToWrite + "rn")
    
    
    totalsWriter.Flush()
    totalsStream.Seek(0, SeekOrigin.Begin)
    
    # create a data source to read in the stream
    textDataSource = TextFileDataSource(totalsStream, readerSettings)
    
    # add the data into a Data Table in Spotfire
    if Document.Data.Tables.Contains(totalsDataTableName):
         Document.Data.Tables[totalsDataTableName].ReplaceData(textDataSource)
    else:
         newTable = Document.Data.Tables.Add(totalsDataTableName, textDataSource)
         tableSettings = DataTableSaveSettings (newTable, False, False)
         Document.Data.SaveSettings.DataTableSettings.Add(tableSettings)
     

    License:  TIBCO BSD-Style License

     

     


    User Feedback

    Recommended Comments

    There are no comments to display.


×
×
  • Create New...