Dynamic column formula

Question

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! Temp.jsl.zip

David_Burnham · Answer

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

]")));

unknown · Answer

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.

ms · Answer

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