nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Using a list of columns for input to Col-functions

JesperJohansen

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.

Accepted answers

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.

All comments

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

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

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.