Using numeric objects in a column equation

[#1]

I need to be able to take a numeric object that increases or decreases in an iterative function and use it in a column formula.  In the equation below, the values of col, l and m need to be evaluated in the column formula.  How can I get this to happen?  Parse(), Eval() and Num() aren't working.

	For( i = 1, i <=8, i++,
	col = 11+i;
	l = col - i;
	m = col + 1 - i; 
	colno = Char(col);
	 New Column("Column "||colno, Character, "Nominal", Formula(If(Row() == 12, Lag(Column(1), l), If(Row() == 13, Lag(Column(2), m), Lag(Column(col, 1)))))));

[#2 · Accepted solution]

This seems to work.  Eval( Eval Expr(....) ) evaluates the expressions before creating the column.

For( i = 1, i <= 8, i++,
	col = 11 + i;
	l = col - i;
	m = col + 1 - i;
	colno = Char( col );
	Eval(
		Eval Expr(
			New Column( "Column " || Expr( colno ),
				Character,
				"Nominal",
				Formula(
					If( Row() == 12,
						Lag( Column( 1 ), Expr( l ) ),
						If( Row() == 13,
							Lag( Column( 2 ), Expr( m ) ),
							Lag( Column( Expr( col ), 1 ) )
						)
					)
				)
			)
		)
	);
);

[#3]

Column( col, 1 )

?

[#4 · Accepted solution]

No variable defined outside of the new column message should be used inside the new column() message.
In my company we have an automatic check that detects such programming issues before executing the code.
If a potential error is detected, a warning message is presented to the user with some suggestions.

Re: Expression Handling in JMP: Tipps and Trapdoors 

For( i = 1, i <= 8, i++,
	col = 11 + i;

	l = col - i;
	m = col + 1 - i;
	colno = Char( col );
	Eval(Eval Expr(New Column( "Column " || colno,
		Character,
		"Nominal",
		Formula(
			If( Row() == 12,
				Lag( Column( 1 ), Expr(l) ),
				If( Row() == 13,
					Lag( Column( 2 ), Expr(m) ),
					Lag( Expr(Name Expr(As Column( col))), 1 )
				)
			)
		)))
	);
);

ouch! As column doesn't understand numbers ...

[#5]

For( i = 1, i <= 8, i++,
	col = 11 + i;
	colname = Column(col) << get name;
	l = col - i;
	m = col + 1 - i;
	colno = Char( col );
	Eval(Eval Expr(New Column( "Column " || colno,
		Character,
		"Nominal",
		Formula(
			If( Row() == 12,
				Lag( Column( 1 ), Expr(l) ),
				If( Row() == 13,
					Lag( Column( 2 ), Expr(m) ),
					Lag( Expr(Name Expr(As Column( colname))), 1 )
				)
			)
		)))
	);
);

[#6]

@hogi 

I get a modification of the script from mmarchandFSLR (shown below) to work just fine, great addition.  Not sure I understand the concern, please clarify?

dt = Current Data Table();

Delete Symbols();

dt = Current Data Table();

For( i = 1, i <= 8, i++,
	c = 11 + i;
	l = 12 - i;
	m = 13 - i;
	colno = Char( c );
	Eval(Eval Expr(New Column( "Column " || colno,
		Character,
		"Nominal",
		Formula(
			If( Row() == 12,
				Lag( As Column( 1 ), Expr(l) ),
				If( Row() == 13,
					Lag( As Column( 2 ), Expr(m) ),
					Lag( As Column( Expr(c)) , 1)
				)
			)
		)))
	);
);

[#7]

First it blew my mind that even after correcting the detail around 

Column( col, 1 )

 @mmarchandFSLR 's solution doesn't seem to work.
Lag() expects As Column(), not a column() ?!??!
This destroys all my "knowledge" of As Column() being used for accessing the value of the current row!

Concerning As Column (col) instead of  Name Expr(As column(col)):

It works - but it's "fragile" against:

- adding  a column (the number doesn't get updated!)

- changed entries (on my system, the entries of the formula column don't get updated?!?)
  [I will delete this one if the situation gets better after a restart]

[#8]

Seems worth to be documented:

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
New Column( "height 2",	Formula( :height ));
New Column( "height 3",	Formula( As Column( "height" ) ));
New Column( "height 4",	Formula( As Column( 4 ) ));
New Column( "height 5",	Formula( Column( 4 )[Empty()] ));
wait(0);

dt[5::10, "height"] = 1;
wait(0);
New window(<< type("modal dialog"), Text Box ("just height 1 got updated ... continue"));


dt << Move Selected Columns( {:height}, after( :name ) );

New window(<< type("modal dialog"), Text Box ("no big difference - now let's recalc"));


dt << rerun formulas();

before                                                                                                   after

                                                   

1. The interpreter of the column formula understands Column(4) and As Column (4), Column("height") and so on ....
   But the mechanism that updates columns if input columns change - doesn't? One has to trigger the update manually?
2. When the column is moved to another place, the number don't get updated.

I will never use numbers in column reference!
I will never use something like Column ("height")

Fun fact:
Formula Editor doesn't like it either:

[#9]

Good catch.

[#10]

I guess I should have described my dark purpose.  I have a table that can't decide between being a tall table and a wide table.  Columns 1 & 2 rows 1-8 are tall, columns 1-11 become a wide table after row 12.  This script is step 1 in knocking some sense into the table.

before                                                                                 after

 

I do a series of further steps to process the data but this was the tricky part.