topic Re: Using numeric objects in a column equation in Discussions

Using numeric objects in a column equation

SpannerHead — Wed, 24 Sep 2025 21:42:40 GMT

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

Re: Using numeric objects in a column equation

mmarchandFSLR — Wed, 24 Sep 2025 21:58:27 GMT

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

Re: Using numeric objects in a column equation

hogi — Thu, 25 Sep 2025 11:49:06 GMT

Column( col, 1 )

Re: Using numeric objects in a column equation

hogi — Thu, 25 Sep 2025 11:50:50 GMT

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.

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

Re: Using numeric objects in a column equation

hogi — Thu, 25 Sep 2025 08:17:06 GMT

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

Re: Using numeric objects in a column equation

mmarchandFSLR — Thu, 25 Sep 2025 11:30:09 GMT

Good catch.

Re: Using numeric objects in a column equation

SpannerHead — Thu, 25 Sep 2025 14:34:57 GMT

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

Re: Using numeric objects in a column equation

hogi — Thu, 25 Sep 2025 20:16:54 GMT

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]

Re: Using numeric objects in a column equation

hogi — Thu, 25 Sep 2025 16:28:06 GMT

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

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?
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:

Re: Using numeric objects in a column equation

SpannerHead — Thu, 25 Sep 2025 20:12:45 GMT

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.