How can retrieve in a calculated column , the column name which has the maximum value for each row ?

[WALID KHALIL]

How can retrieve in a calculated column , the column name which has the maximum value for each row ?

[Gaia Paolini]

I agree it would be nice to have an expression directly in Spotfire to do that.

I am not aware it exists, you could add it to the TIBCO ideas portal (ideas.tibco.com).

Some possible solutions:

1. if you only have a handful of columns, then creating a calculated column directly is the simplest way:

Casewhen [Measurement1] = Max([Measurement1],[Measurement2],[Measurement3]) then "Measurement1"when [Measurement2] = Max([Measurement1],[Measurement2],[Measurement3]) then "Measurement2"when [Measurement3] = Max([Measurement1],[Measurement2],[Measurement3]) then "Measurement3"end

2. If you have many columns and not too many rows, you could do it with a simple TERR (or Python) data function: below the TERR version:

max_col_name=colnames(df)[apply(df,1,which.max)]

where input is the data table (df) with the columns you are considering, and output is max_col_name of type Column.

3. If you have many columns and many rows (so maybe a data function would be non performant) you could construct the expression 1 automatically in an Iron Python script:

from Spotfire.Dxp.Data import *table=Document.Data.Tables["your table name"]cols=table.Columns #find names of numeric columnscol_names=[]for col in cols: dataType=col.Properties.DataType if dataType.IsNumeric: col_names.append(col.Name) #merge them in a stringcol_names_concat=','.join(col_names) #create an expression to calculate the max value across all numeric columns in a rowmax_expr='Max('+col_names_concat+')' #create expression for finding column name corresponding to max valueexpr = 'CASE 'for col_name in col_names: expr += 'when ['+col_name+'] = '+max_expr+' then "'+col_name+'"'expr += ' END' #add calculated column MAX_COLUMN#if it exists (generating exception) remove it then re-add itnew_column_name='MAX_COLUMN'try: new_column = cols.AddCalculatedColumn(new_column_name,expr)except: cols.Remove(new_column_name) new_column = cols.AddCalculatedColumn(new_column_name,expr)

[WALID KHALIL]

Hello Gaia,

The first option worked for me perfectly.

Many Thanks for your assistance

Best regards