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

Question

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?

jthi · Answer

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")

txnelson · Answer

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] ) );

dadawasozo · Answer

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")