topic Re: Best Way To Write A Formula With A Potentially Missing Column Reference in Discussions

Best Way To Write A Formula With A Potentially Missing Column Reference

SpannerHead — Thu, 23 Apr 2026 20:41:24 GMT

I have a script that creates a formula based on the contents of an object. In some instances, the entry that fills in the object is bypassed. N Items(yVars) ==0 in that case. How can I have the formula calculate without the column reference if empty?

The case below is overly complex where I check for the presence of an entry and use an If() statement to create alternative actions.

If( N Items( yVars ) == 0,
	Data Table( "HIRP" ) << New Column( "Average PTC",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			Col Mean(
				Col Maximum( :Power, :Heater, :HD_NUM ),
				:Heater,
				:WAFER_SUBSTRATE_LOT_ID_IDX
			)
		)
	),
	Eval(
		Eval Expr(
			Data Table( "HIRP" ) << New Column( "Average PTC",
				Numeric,
				"Continuous",
				Format( "Best", 12 ),
				Formula(
					Col Mean(
						Col Maximum( :Power, :Heater, :HD_NUM ),
						:Heater,
						Expr(
							Name Expr( As Column( Divider ) )
						)
					)
				)
			)
		)
	)
);

Re: Best Way To Write A Formula With A Potentially Missing Column Reference

ih — Thu, 23 Apr 2026 22:30:37 GMT

Here is one way:

//first a table to add columns to
Names default to here(1);
dt = New Table( "Test Table",
	Add Rows( 2 ),
	New Column( "a", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2] ) )
);

//this one does create a column
if(!Contains(dt << get column names("string"), "b"),
	dt << New Column( "b", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [3, 4] ) )
);

//this does not since b already exists
if(!Contains(dt << get column names("string"), "b"),
	dt << New Column( "b", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [5, 6] ) )
);

Re: Best Way To Write A Formula With A Potentially Missing Column Reference

jthi — Fri, 24 Apr 2026 04:16:07 GMT

Is there a specific reason to avoid doing what you are currently? If column reference is empty, don't add it to the formula.

There really isn't anything complex in your way of doing it and it is easy to read and understand. You could use if-statement to just set the formula which will reduce the repetition slightly. I would use either this or the way you are doing it (if I understand your question correctly)

Names Default To Here(1); 
// myvar = 1;

dt = Open("$SAMPLE_DATA/Big Class.jmp");

newcol = dt << New Column("Test", Numeric, Continuous);

If(Is Missing(myvar),
	newcol << Set Formula(
		:height/:weight
	);
,
	newcol << Set Formula(
		:weight/:height
	)	
);

Some more difficult to read, understand and possibly debug options:

You can build the formula expression in if statement and then set the formula

Names Default To Here(1); 
myvar = 1;
dt = Open("$SAMPLE_DATA/Big Class.jmp");

fexpr = If(Is Missing(myvar), Name Expr(:height/:weight), Name Expr(:weight/:height));

Eval(EvalExpr(
	newcol = dt << New Column("Test", Numeric, Continuous, Formula(
		Expr(NameExpr(fexpr))
	))
));

Or you can use the if statement inside your formula evaluation (unnecessary complicated in my opinion)

Names Default To Here(1); 

dt = Open("$SAMPLE_DATA/Big Class.jmp");

newcol = dt << New Column("Test", Numeric, Continuous);

Eval(Substitute(
	Expr(newcol << Set Formula(
		_fexpr_
	)),
	Expr(_fexpr_), 	If(Is Missing(myvar), Name Expr(:height/:weight), Name Expr(:weight/:height))
));

Re: Best Way To Write A Formula With A Potentially Missing Column Reference

SpannerHead — Fri, 24 Apr 2026 13:40:53 GMT

I think my basic objection to the current method is that it looks bloated. There are 2 iterations of the same code with and without the intermittent column. I'd have thought there would have been something analogous to a Try() statement to deal with the inconsistency. Something of that kind would also make the editing process easier.

Re: Best Way To Write A Formula With A Potentially Missing Column Reference

jthi — Fri, 24 Apr 2026 16:12:47 GMT

You cannot really deal with missing column reference inside the formula (you could use Try() but it will most likely make the formula very slow and I wouldn't do it). I did provide some options to avoid the replication. You could also create a function to create the column/add the formula.