topic Re: create new column and set each row value from different column based on another column (column name variable) in Discussions

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

dadawasozo — Mon, 25 Aug 2025 23:47:20 GMT

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?

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

txnelson — Tue, 26 Aug 2025 03:25:01 GMT

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.

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

dadawasozo — Tue, 26 Aug 2025 03:35:07 GMT

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

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

dadawasozo — Tue, 26 Aug 2025 03:46:37 GMT

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

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

txnelson — Tue, 26 Aug 2025 04:40:48 GMT

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

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

jthi — Tue, 26 Aug 2025 04:31:05 GMT

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