Choose Language Hide Translation Bar
Highlighted
JesperJohansen
Level IV

Using a list of columns for input to Col-functions

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
Highlighted
ms
Super User ms
Super User

Re: Using a list of columns for input to Col-functions

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.

View solution in original post

3 REPLIES 3
Highlighted
ms
Super User ms
Super User

Re: Using a list of columns for input to Col-functions

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

Highlighted
ms
Super User ms
Super User

Re: Using a list of columns for input to Col-functions

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.

View solution in original post

Highlighted
JesperJohansen
Level IV

Re: Using a list of columns for input to Col-functions

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
Article Labels

    There are no labels assigned to this post.