Referencing columns from other tables

bwkeller2

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

txnelson

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

1. You need to << Add Rows() to the empty data table;
2. 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
3. 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");