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]

[Gaia Paolini]

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:

https://community.spotfire.com/s/article/Replace-specific-values-of-a-Data-Table-column-in-TIBCO-Spotfire-using-IronPython

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)

[Gaia Paolini]

If you have other transformations other than the null-replacement ones (check your data canvas) then the dataOperation.ReplaceTransformations("") statement might remove those as well, so if this is a problem you can comment it out.

[giuseppe valenti]

Thanks,i will try to apply the script.