WALID KHALIL Posted April 26, 2023 Share Posted April 26, 2023 How can retrieve in a calculated column , the column name which has the maximum value for each row ? Link to comment Share on other sites More sharing options...
Gaia Paolini Posted April 27, 2023 Share Posted April 27, 2023 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"end2. 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) Link to comment Share on other sites More sharing options...
WALID KHALIL Posted April 27, 2023 Author Share Posted April 27, 2023 Hello Gaia,The first option worked for me perfectly. Many Thanks for your assistanceBest regards Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now