Help with column formula based on column locations?

hacketkm

Hello,

I have a list of strings that are column names. I want to determine their column location(i) in a data table and then add a new column with the formula( Column( i ) + Column( i+1 )). My code looks like this:

A	B	C	D	E	F
1	2	3	4	5	6
1	2	3	4	5	6
1	2	3	4	5	6

cols = dt << Get Column Names( St

ring);

list = { "B", "D"};

n = N Items( list);

locate = {}:

For( i=1, i<=n, i++,

     locate = Loc( dt, list);

     New Column( "New", Formula( Column( locate) + Column( locate + 1)))

);

I am having trouble because Loc() returns a matrix and Column() can only evaluate a number. I want my final data table to look like this:

A	B	C	D	E	F	New	New 2
1	2	3	4	5	6	5	9
1	2	3	4	5	6	5	9
1	2	3	4	5	6	5	9
1	2	3	4	5	6	5	9

Can anyone help me out?

ms

A formula column will naturally not work if it's dependent columns are removed. If you want to delete the columns that are used by the formula, then what's the point of using column formulas in the first place? If you're only after the sum and not interested in the dynamic advantages that a column formula offers, I suggest you don't use formulas (which also make this a lot easier task).

Try this on the table above:

list = {"B", "D"};

locate = Expr( Loc( cols, list[i] )[1] );

For( i = 1, i <= N Items( list ), i++,

  cols = dt << Get Column Names( String );

  col = dt << New Column( "New", numeric );

  For Each Row( col[] = Column( locate )[] + Column( locate + 1 )[] );

  dt << delete columns( Column( locate ), Column( locate + 1 ) );

  col << set name( list[i] );

);