Subscribe Bookmark RSS Feed

Using a list of columns for input to Col-functions

JesperJohansen

Community Trekker

Joined:

Apr 15, 2013

I am trying to create a script to generate new columns based on user input. The input comes from a dialog box, and is in the form of a list of column names, e.g.:

Cols={:Data,:By 1,:By 2};

I want top create a new column "n" with a formula containing the Col Number formula:

New Column("n",formula(Col Number(:Data,:By 1,:By 2)));

However I am stuck at getting the formula:

Col Number({:Data,:By 1,:By 2})

that is with curly brackets. No error is returned, but the formula won't evaluate.

How do I use the list of columns as input for a Col-function?

The number of columns must be allowed to vary.

BR
Jesper
1 ACCEPTED SOLUTION

Accepted Solutions
ms

Super User

Joined:

Jun 23, 2011

Solution

Alternatively, instead of building an Expression as above, the list content can be incorporated into a parseable string. In this example, the curly brackets are removed using substitute.

Cols = {:Data, :By 1, :By 2};

s = "New Column( \!"n\!", formula( Col Number(" || Substitute( Char( Cols ), "{", "", "}", "" ) || ") ) )";

Eval( Parse( s ) );

The string approach is straightforward and do usually work, but code can be frustrating to debug if the strings grow large.

3 REPLIES
ms

Super User

Joined:

Jun 23, 2011

Here is one way to do it:

Cols = {:Data, :By 1, :By 2};

f = Expr( Col Number() );

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

  Insert Into( f, Cols[i] )

);

New Column( "n", formula( Name Expr( f ) ) );

ms

Super User

Joined:

Jun 23, 2011

Solution

Alternatively, instead of building an Expression as above, the list content can be incorporated into a parseable string. In this example, the curly brackets are removed using substitute.

Cols = {:Data, :By 1, :By 2};

s = "New Column( \!"n\!", formula( Col Number(" || Substitute( Char( Cols ), "{", "", "}", "" ) || ") ) )";

Eval( Parse( s ) );

The string approach is straightforward and do usually work, but code can be frustrating to debug if the strings grow large.

JesperJohansen

Community Trekker

Joined:

Apr 15, 2013

Thank you very much Your first suggestion is the more elegant one and solved my original problem, but your second suggestion solved a second problem of mine, I didn't yet pose.

BR
Jesper