topic Re: Referencing columns from other tables in Discussions

Referencing columns from other tables

bwkeller2 — Sat, 10 Jun 2023 23:16:42 GMT

I'm trying to make a new table by reading from two source tables. I'd like to avoid joining them into one big table and manipulating from there. The new table will be an array of deltas between the two source tables. The script is creating the columns just fine, but it's not populating them with anything. I'm assuming there's some syntax i'm missing in referencing columns from other tables in my formula.

predt = Open("C:\...\pre_data.jmp");
predtcols = predt << Get Column Names (string);
postdt = Open("C:\...\post_data.jmp");
postdtcols = postdt << Get Column Names (string);
dt = new table ("delta_values");
for (i=1, i <= NCols(predt), i++,
	dt << New column ("delta_"||predtcols[i], numeric, continuous, formula(postdt:column(postdtcols[i]) - predt:column(predtcols[i])));
	);

Re: Referencing columns from other tables

txnelson — Thu, 23 Jul 2020 00:59:02 GMT

You had a couple of issues that were byting(pun) you in the butt.

You need to << Add Rows() to the empty data table;
The format for using the Column() function is not postdt:column( postcols[i] ). If this would be the method to use in this situation, the form would be Column( postdt, postcols[i] ). But that is not what is needed here
Assigning formulas in New Column() operations are a bit tricky. The Formula() that is set is not evaluated before it is made the formula. That is, if you place postcols[i] into a formula, it does not look up the value of postcols[i] as it is building the formula, it just places postcols[i] into the formula, and then attempts to execute it when the formula is run. The issue with this, is that since the variable "i" is being reassigned over and over in a loop, everyone of the formulas that has "i" referenced in it, gets changed every time "i" is changed within JSL. That is not what you want. So what needs to be done, is the evaluate in JSL the exact string of code that you want, and then run that JSL to create the new column. You can see that in the corrected JSL below

predt = data table("one");//Open( "C:\...\pre_data.jmp" );
predtcols = predt << Get Column Names( string );
postdt = data table("two");//Open( "C:\...\post_data.jmp" );
postdtcols = postdt << Get Column Names( string );
dt = New Table( "delta_values" );
dt << add rows(nrows(postdt));
For( i = 1, i <= N Cols( predt ), i++,
	Eval(
		Parse(
			"dt << New Column( \!"delta_" || predtcols[i] || "\!",
		numeric,
		continuous,
		formula( postdt:" ||
			postdtcols[i] || "[Row()] - predt:" || predtcols[i] || "[Row()]) )'"
		)
	)
);
dt << delete columns("column 1");

Re: Referencing columns from other tables

bwkeller2 — Thu, 23 Jul 2020 13:40:03 GMT

Thanks for the quick response! It looks like i'm only getting the first two columns' deltas with this script. The columns are formatted the same as the rest (numeric, continuous) so i'm not sure what's going on here.

Re: Referencing columns from other tables

txnelson — Thu, 23 Jul 2020 14:24:36 GMT

I assume that it is a data issue. The code worked fine for my test case with 2 columns, but it is written to handle as many columns as it finds in the variable "predt"

For( i = 1, i <= N Cols( predt ), i++,

So, what you need to do, is to step through the code and check the variable values and see what is happening. Without your data being posted to this discussion, I don't know what is going on.

Are there any messages being displayed to the Log?

Re: Referencing columns from other tables

bwkeller2 — Thu, 23 Jul 2020 16:46:21 GMT

ah. it looks like all of my columns after column 2 have a (xxx) in the name - the units of the measurement. That could be it? The log is throwing an error for every column like that

Re: Referencing columns from other tables

Mark_Bailey — Thu, 23 Jul 2020 16:53:28 GMT

The units (defined as a column property) are not part of the name of the column.

Re: Referencing columns from other tables

bwkeller2 — Fri, 24 Jul 2020 13:14:10 GMT

I imported the column names from and excel sheet (output of our tester), so I think they're buried in the column names

Re: Referencing columns from other tables

txnelson — Fri, 24 Jul 2020 14:47:54 GMT

That is easy to check. Double cliick on the column header in JMP and it will open the Col Info window. It will have the real column name listed.