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

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

Greenhorn — Fri, 09 Jun 2023 18:03:22 GMT

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?

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

jthi — Thu, 21 Oct 2021 16:15:17 GMT

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.

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

Greenhorn — Sat, 23 Oct 2021 14:51:12 GMT

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?

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

jthi — Mon, 25 Oct 2021 07:07:56 GMT

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

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

Greenhorn — Mon, 25 Oct 2021 11:27:05 GMT

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!

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

jthi — Mon, 25 Oct 2021 12:07:46 GMT

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: