Creating a table with formula in the columns - Random Normal() - from external jmp

[#1]

hi,

Can somebody help me with a JSL code, which generates a new table with a formula inside the columns properties?

The table size, column names and parameters for the formula comes from an external jmp file like attched.

Can someone share some code snippest, how to start with it?

[#2 · Accepted solution]

You can loop over your RandomNormal_dt.jmp with For Each Row and then get values from the columns. Use Eval(EvalExpr()) to add values to Formula.

Names Default To Here(1);

dt_random = New Table("RandomNormal_dt",
	Add Rows(20),
	New Column("Analysis Columns",
		Character(6),
		"Nominal",
		Set Values(
			{"Name1", "Name2", "Name3", "Name4", "Name5", "Name6", "Name7", "Name8", "Name9", "Name10", "Name11", "Name12", "Name13", "Name14","Name15", "Name16", "Name17", "Name18", "Name19", "Name20"}
		),

	),
	New Column("Mean", Numeric, "Continuous", Format("Best", 6), Set Values([1, 1, 0, 0, 0, 2, 2, 3, 3, 5, 10, -5, -6, 3, 3, 3, 3, 3, 0.5, 0.5]), ),
	New Column("Std Dev",
		Numeric,
		"Continuous",
		Format("Best", 6),
		Set Values(
			[0.0059167075785471, 0.00455419279075633, 0.00421951193571742, 0.00421951193571742, 0.00440945078910684, 0.00440945078910684,
			0.0102216984358101, 0.00419398115162298, 0.0808821732109718, 0.0876924473662647, 0.119338972830502, 0.0918654224334168,
			0.0921864791037008, 0.0868549769597964, 0.0808821732109718, 0.0865262154672911, 0.186459394109712, 0.189566585675401, 0.0876924449303901,
			0.0434724818197371]
		),

	)
);

//create collection table
dt = New Table("Analysis");

//lets use formulas, we can easily add more rows if needed
For Each Row(
	dt_random,
	columnName = As Column(dt_random, "Analysis Columns");
	meanValue = As Column(dt_random, "Mean");
	stdDevValue = As Column(dt_random, "Std Dev");
	
	//use eval expr to get values in formula
	Eval(Eval Expr(dt << New Column(columnName, Numeric, Continuous, Formula(Random Normal(Expr(meanValue), Expr(stdDevValue))))));
);

Then you can add rows as needed.After adding rows you can remove formulas to make the datatable faster if needed. If you know how many rows you need, you can replace Formula() with << Set Each Value() and create the collection with enough rows.

[#3]

Thanks! It worked with this code right below. And it helps me a lot!!

However, I'm motivated to learn a bit JSL now. And I tought about a possible implementation. Second code.

Clear Globals();
Names Default To Here( 1 );

//mydt = 
//Data Table( "dt_RandomNormalParameter" ) <<
mydt = Current Data Table();

// New data table
my_newdt = New Table( "Table Name", Add Row ( 1000 ));

//lets use formulas, we can easily add more rows if needed
For Each Row(
	mydt,
	columnName = As Column(mydt, "Analysis Columns");
	meanValue = As Column(mydt, "Mean");
	stdDevValue = As Column(mydt, "Std Dev");
	
	//use eval expr to get values in formula
	Eval(Eval Expr(my_newdt << New Column(columnName, Numeric, Continuous, Formula(Random Normal(Expr(meanValue), Expr(stdDevValue))))));
);

// Code adds Column "Column 1"

my concept to solve my problem:

Clear Globals();
Names Default To Here( 1 );

mydt = Current Data Table();

// New data table
my_newdt = New Table( "Table Name",
Add Rows (N Rows ( mydt )), // row count can be set by hand e.g. 1000
	
//For ( i = 1, i <= N Rows ( mydt ), i++, // for loop is not executed

New Column( Column(mydt, 1 )[1] , // Loop i==1
	Numeric,
	"Continuous",
	Format( "Best", 6 ),
	Formula( Random Normal( Column(mydt, :Mean )[1], Column(mydt, :Std Dev )[1] ) )// Column(mydt, :Mean )[1] is not evalueted
				),
New Column( Column(mydt, 1 )[2] , // Loop i ==2
	Numeric,
	"Continuous",
	Format( "Best", 6 ),
	Formula( Random Normal( Column(mydt, :Mean )[2], Column(mydt, :Std Dev )[2] ) )//// Column(mydt, :Mean )[1] is not evalueted
				)				
				
//)// for loop

)// New data table

Unfortunatelly, I don't know how to use "for loop" inside " New Table" and evaluete index  inside Formula.

I assume some how with Eval, Eval Expr.

But I don't understand the syntax.

Do you believe my concept could work with proper syntax as well?

What needs to be modified?

[#4]

Is there a specific reason why you would want to create the columns inside New Table()? I'm not sure of it is possible and if it is, it will most likely make the code messier when compared to creating them after New Table().

[#5]

The actual script generates a undefined column " Clolumn 1". 

And generating columns inside New Table () doesn't create such column. It is just cosmetic...

in mean time, I have a script which has single column definition and the others are added outside the new column.

And the new generated table has correct calculated values. However, if I look inside the Formula then it contains the expression from the script.

I can mask this fact by using Set Each Value(). But it makes me crazy ...

And now I'm obsessive to understand why the expression is not evaluated properly. I didn't success using Eval () Eval Expr() and Expr(). I believe to undestand their functions but with combination with Formula, no chance.

However, my initial problem is solved with your proposal. Thanks!

[#6]

Removing the Column 1 is easy in script so it shouldn't be a problem. I would either create column with specific name or remove it by using index

Names Default To Here(1);
dt = New Table("testtable", add rows(1),
	new column("REMOVETHIS")
);
wait(1); //for demonstration purposes
//dt << Delete Column(1);
dt << Delete Columns("REMOVETHIS");

Using Set Each value is better if you have no need for formulas, as it is faster.

For learning... Eval(EvalExpr()) definitely aren't the easiest things to understand, but there are some content which might help. Quickly checking I found these:

• JMP Help - Lists and Expressions 
• Introspection 
• Using JSL to Develop Efficient, Robust Applications (EU 2018 415) 
• JMP 16 Scripting Guide 
• Expression Handling Functions: Part I - Unraveling the Expr(), NameExpr(), Eval(), ... Conundrum (to... 
• JSL Companion book