create new column and set each row value from different column based on another column (column name variable)

[#1]

Hi,

I have data similar to example in picture. first 6 columns are related. I created MaxCol to find out who has the Max which based on first 3 columns, then I want to create MaxValFromCol column where it take value from one of the column from 4-6. it will pick value from column 4-6 based on MaxCol. I tried to set the value of MaxValFromCol using Column(char(:MaxCol) || "_t"), but not success. Can someone give some suggestion how we can take a column value as column name and set value based on it?

[#2]

Here is the formula I came up with for MaxCol

As Constant(
	startCol = 1;
	endCol = 3;
	nCol = (endCol - startCol) + 1;
	dt = Current Data Table();
	colNames = dt << get column names;
);
theMax = Loc( dt[Row(), Index( startCol, endCol )], Max( dt[Row(), Index( startCol, endCol )] ) );
theMax = (theMax + startCol) - 1;
colNames[theMax][1];

and here is the formula for MaxValFromCol

As Constant(
	startCol = 1;
	endCol = 3;
	nCol = (endCol - startCol) + 1;
	dt = Current Data Table();
	colNames = dt << get column names();
);
theMax = Loc( dt[Row(), Index( startCol, endCol )], Max( dt[Row(), Index( startCol, endCol )] ) )
+ncol;
Parse( Char( colNames[themax][1] ) );

It is late at night, and I am sure others will have better solutions.

[#3]

Hi Jim,

I able to get the MaxCol What I want the MaxValFromCol to be is the value from column 4-6, not the column name. For example, row 1 i MaxCol =="Lily", then the row 1 of MaxValFromCol will take value from Lily_t, which is 4|5. the Row 2 is 4|4 because row 2 of MaxCol is Mike, so that 4|4 come from Mike_t

[#4]

for only few column I think I can use if condition like below, but I have too many columns. So I wonder if there is a wayto use MaxCOl value as variable to call the column.

if only few columns, below can work:

if(:MaxCol =="Lily", :Lily_t,

:MaxCol =="Mike", :Mike_t,

:MaxCol =="Dan", :Dan_t,

)

hoping to have something simple as below when I have hundreds of columns

MaxValFromCol == Column(char(:MaxCol)||"_t")

[#5]

My error.......late at night......I will correct.

Here are the 2 formulas that I have working

As Constant(
	startCol = 1;
	endCol = 3;
	nCol = (endCol - startCol) + 1;
	dt = Current Data Table();
	colNames = dt << get column names( string );
);
theMax = Loc( dt[Row(), Index( startCol, endCol )], Max( dt[Row(), Index( startCol, endCol )] ) );
theMax = (theMax + startCol) - 1;
x = colNames[theMax][1];
x;

As Constant(
	startCol = 1;
	endCol = 3;
	nCol = (endCol - startCol) + 1;
	dt = Current Data Table();
	colNames = dt << get column names( string );
);
theMax = Loc( dt[Row(), Index( startCol, endCol )], Max( dt[Row(), Index( startCol, endCol )] ) )
+ncol;
Eval( Parse( "x=:" || colNames[theMax][1] ) );

[#6]

Not really sure I would use something like this, but it might work

As Column(Column(:Column 1 ||"_t"))

You can also most likely drop the Column()

As Column(:Column 1 || "_t")