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

Question

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

jthi · Answer

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.

SpannerHead · Answer

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.

jthi · Answer

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