Jump to content
We've recently updated our Privacy Statement, available here. ×

Hello,is there an easy way to fill empty values with zero on multiple columns(BINx)? The number of columns change from time to time,can be also several hundreds of columns,the column name has always the format bin1,bin2,..,binN. See image example.

giuseppe valenti

Recommended Posts

The fastest way to this would be to run an IronPython script. Run it every time you replace your data table.

The data table needs to be loaded as 'Always new data' or 'New data when possible' for this to work, otherwise if set as 'Stored' you would get the error:

Could not execute script 'replaceNulls': Not possible since an operation with DataLoadingBehavior set to Stored will not be refreshed.

Apart from that, this should work. Idea taken from this article:


Although there is an error in that script, as the transformations were loaded before being cleared (so nothing would be actually cleared).

  • I added a few lines to identify the columns to transform as columns with names containing 'BIN' followed by one or more integers. If not the case, please modify the pattern.
  • Also, I am assuming they are all of data type Real.
  • Please replace the name of the data table (currently hard-coded as 'data') with yours.
# Copyright © 2023. TIBCO Software Inc. Licensed under TIBCO BSD-style license. from Spotfire.Dxp.Data import *from Spotfire.Dxp.Data.Transformations import *import re ## Replace data table name heredataTable=Document.Data.Tables['data'] ## Identify BINx columnsselected_columns = []pattern = re.compile('^BIN\d+')for col in dataTable.Columns: if(re.findall(pattern,col.Name)!=[]): selected_columns.append(col.Name) ## If there are BIN columns, replace nulls with 0sif len(selected_columns)>0: # Replace Values Transformation dataOperation = dataTable.GenerateSourceView().OperationsSupportingTransformations[0] # Clear all transformations first dataOperation.ReplaceTransformations("") # Then get a handle on the transformations object transformations = dataOperation.GetTransformations(); for col in selected_columns: col_signature = DataColumnSignature(col,DataType.Real); t=ReplaceValuesTransformation(col_signature, None, 0.0) transformations.Add(t)  dataOperation.ReplaceTransformations(transformations)
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...