Subscribe Bookmark RSS Feed

Help with column formula based on column locations?

hacketkm

Community Trekker

Joined:

Sep 14, 2012

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:

ABCDEF

1

23456
123456
123456

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:

ABCDEFNewNew 2
12345659
12345659
12345659
12345659

Can anyone help me out?

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

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

);

4 REPLIES
ms

Super User

Joined:

Jun 23, 2011

To get column variables into column formulas by jsl is not straightforward. The formula must look exactly as the final expression, i.e nothing is evaluated automatically. The trick is to define and manipulate expressions (or strings) and then use the right combination of eval, parse etc.

Below is an example that seem to work. It's a bit circular as it locates the columns by its name and than gets it name again which then is parsed. Pew! I could not find an simpler procedure, but is interested if anyone has a more elegant solution idea.

//make the example table

dt = New Table( "test",

  Add Rows( 3 ),

  New Column( "A", Numeric, Continuous, Format( "Best", 1 ), Set Values( [1, 1, 1] ) ),

  New Column( "B", Numeric, Continuous, Format( "Best", 12 ), Set Values( [2, 2, 2] ) ),

  New Column( "C", Numeric, Continuous, Format( "Best", 12 ), Set Values( [3, 3, 3] ) ),

  New Column( "D", Numeric, Continuous, Format( "Best", 12 ), Set Values( [4, 4, 4] ) ),

  New Column( "E", Numeric, Continuous, Format( "Best", 12 ), Set Values( [5, 5, 5] ) ),

  New Column( "F", Numeric, Continuous, Format( "Best", 12 ), Set Values( [6, 6, 6] ) )

);

//Script starts here

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

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

// Locate column expr

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

// New column with formula expr

f = Expr( New Column( "New", Formula( Expr( Parse( Column( locate ) << get name ) )

+ Expr( Parse( Column( locate + 1 ) << get name ) ) ) ) );

//Loop over list

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

  Eval( Eval Expr( f ) )

);

hacketkm

Community Trekker

Joined:

Sep 14, 2012

Thank you so much! This approach works very well.

I also have a follow-up question. I would like to make some changes to the columns of this new table:

ABCDEFNewNew 2

1

2345659
12345659
12345659

Is there a way to remove the 4 columns that were summed to create the "New" and "New 2" columns and rename them using list = { "B", "D"} so that the resulting table looks like this: 

AFBD
1659
1659
1659
Solution

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

);

hacketkm

Community Trekker

Joined:

Sep 14, 2012

Thank you for your help. I really appreciate it.