topic Dynamic column formula in Discussions

Dynamic column formula

Anonymous — Mon, 05 Nov 2012 18:56:39 GMT

I am generating new columns by adding some columns. The columns to add, I get the names from a file and build the names as a array list. The column names and number of columns could change. When the number of columns change, I am having to edit the formula to reflect the change. Using the column names array list, is there a way of dynamically adding them in the formula, without having to specify every column I want to add,

dt=current data table();

Cols=Associative Array();

Cols["A1"]={ColA, ColB, ColC};

Cols["A2"]={ColE, ColF, ColG};

Cols["abc"]={"Total1", Total2};

newcolExpr = Expr (

dt << New Column(Counts["abc"][1], Numeric, Continuous, Formula( If(Expr(Cols["A1"][1]) + Expr(Cols["A1"][2]) + Expr(Cols["A1"][3]) > 0,1,0)));

dt << New Column(Counts["abc"][2], Numeric, Continuous, Formula( If(Expr(Cols["A2"][1]) + Expr(Cols["A2"][2]) + Expr(Cols["A2"][3]) > 0,1,0)));

);

Eval(Eval Expr( newColExpr));

I would appreciate any suggestions for my problem.

Thanks!

Re: Dynamic column formula

ms — Tue, 06 Nov 2012 20:36:25 GMT

One way to dynamically build expressions from strings. In comparision to expressions, I think strings are more easy to manipulate.

Here an example. It "must" be a simpler way but it works.

Clear Globals();

//Make example table

dt = New Table( "test",

Add Rows( 3 ),

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

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

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

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

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

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

);

//define the criteria array

Cols = Associative Array();

Cols["A1"] = {A, B, C};

Cols["A2"] = {D, E, F};

Cols["abc"] = {"Total1", "Total2"};

//Make substrings iteratively

s1 = "Expr(Cols[\!"A1\!"][1])";

For( i = 2, i <= N Items( Cols["A1"] ), i++,

s1 = s1 || " + " || Substitute( "Expr(Cols[\!"A1\!"][_i_])", Expr( _i_ ), Char( Eval( i ) ) )

);

s2 = "Expr(Cols[\!"A2\!"][1])";

For( i = 2, i <= N Items( Cols["A2"] ), i++,

s2 = s2 || " + " || Substitute( "Expr(Cols[\!"A2\!"][_i_])", Expr( _i_ ), Char( Eval( i ) ) )

);

// Strings representing expressions, substitute _s_ for substrings

newcol_s1 = "dt << New Column( Cols[\!"abc\!"][1], Numeric, Continuous, Formula( If( _s_ > 0, 1, 0 ) ) )";

Substitute Into( newcol_s1, Expr( _s_ ), s1 );

newcol_s2 = "dt << New Column( Cols[\!"abc\!"][2], Numeric, Continuous, Formula( If( _s_ > 0, 1, 0 ) ) )";

Substitute Into( newcol_s2, Expr( _s_ ), s2 );

//Parse complete strings

e1 = Parse( newcol_s1 );

e2 = Parse( newcol_s2 );

//Make new columns by evaluating the substring expr and then the complete expressions.

Eval( Eval Expr( e1 ) );

Eval( Eval Expr( e2 ) );

Re: Dynamic column formula

Anonymous — Wed, 07 Nov 2012 06:17:06 GMT

Hi MS,

Thanks for the detailed example. Lots of manipulation involved with strings, but I can't see any other simpler way to do this.

Re: Dynamic column formula

David_Burnham — Wed, 14 Nov 2012 22:25:26 GMT

There are a couple of notations that you can use which can often help dynamic jsl more readable:

If you need to include quotes in strings, instead of using the \!" notation which becomes tedious you start the string with "\[ ... then all subsequent escaped characters dont need the \! notation - you finish the string with ]"

For example:

str = "\[ "Hello World" ]\";

show(str);

show(Parse(str))

Secondly, if you are constructing a jsl command dynamically using variables, you can use the EvalInsert function that allows you to insert a variable using the notation ^my_variable^. This avoids the need for using SubstituteInto.

Combining the two methods you can wrap dynamic code using the following pattern:

Eval(Parse(EvalInsert("\[

jsl-statements here using ^var^ notation for subtitution

]\")));

For example:

Clear Globals();

//Make example table

dt = New Table( "test",

Add Rows( 3 ),

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

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

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

);

//define the criteria array

Cols = Associative Array();

Cols["A1"] = {"A", "B", "C"};

Cols["abc"] = {"Total1", "Total2"};

//Make substrings iteratively

Eval(Parse(EvalInsert("\[

s1 = Cols["A1"][1];

]\")));

For( i = 2, i <= N Items( Cols["A1"] ), i++,

Eval(Parse(EvalInsert("\[

s1 = s1 || "+" || Cols["A1"][^i^]

]\")));

);

Eval(Parse(EvalInsert("\[

newcol_s1 = dt << New Column( Cols["abc"][1],

numeric, continuous,

formula( If (^s1^ > 0, 1, 0 ) )

]\")));