topic How to catch formula error in Discussions

How to catch formula error

antonio-domenic — Fri, 09 Jun 2023 17:08:34 GMT

I would catch errors when a formula contains missing column. In the following example, a table contains 2 columns. Adding a new column that uses a non existing column, the result is missing result (correctly) and an error that block my script that I am not able to catch because try statement doesn't work. Is there a workaround for this?

dt= New Table( "Test",
	Add Rows( 1 ),
	New Column( "A",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [2] )
	),
	New Column( "B",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [1] )
	)
);

formula=":Name(\!"C\!") - :Name(\!"B\!")";
try(
	eval(parse("dt << new column(\!"value\!", Numeric, \!"Continuous\!", Formula(" ||formula || "))"));
,
	error="missing column";
);

Re: How to catch formula error

txnelson — Mon, 29 Aug 2022 09:47:44 GMT

I am not exactly sure what you want the final outcome to be, if the column is missing, but the below is one way to handle the issue

dt = New Table( "Test",
	Add Rows( 1 ),
	New Column( "A",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [2] )
	),
	New Column( "B",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [1] )
	)
);

If( Try( dt:c << get name, "" ) == "",
	type = "character";
	formula = "\!"missing column\!"";
,
	type = "numeric/continuous";
	formula = ":Name(\!"C\!") - :Name(\!"B\!")";
);


Eval(
	Parse(
		"dt << new column(\!"value\!"," || type || ", Formula(" || formula
		 || "))"
	)
);

Re: How to catch formula error

antonio-domenic — Mon, 29 Aug 2022 11:15:18 GMT

Thank you, but it is not so useful. The problem is that I extract automatically some data (by script) and any of them can be missing (but I don't know in advance if and which). The script stops when missing column is found and this is the real problem. A good solution could be the try statement, but it doesn't work.

Re: How to catch formula error

pauldeen — Mon, 29 Aug 2022 13:43:12 GMT

This might be better to test before you make the formula column. So do an if statement to see if the column exists...if it doesn't, don't make the formula column.

Re: How to catch formula error

ErraticAttack — Tue, 30 Aug 2022 19:45:15 GMT

@antonio-domenic , I believe that what you're wanting is to determine, before attempting to create a column based on a formula, whether the table contains the appropriate columns for the given formula? If you're able to determine this before attempting to create the column you'll have less likelihood of running into script-stopping errors.

The good news is that it's pretty simple to determine all of the external names a formula relies on -- which should be only column names of the current data table. (It's best practice to not have a formula rely on a currently in-scope variable that will lose scope at some point in the future, causing the formula to error out).

Here's some example code with a function that recursively parses expressions (formulas are of course just expressions) for any dangling names. You can then check the column names of the table and apply if the column names satisfy the formula:

Names Default to Here( 1 );

dt= New Table( "Test",
	Add Rows( 1 ),
	New Column( "A",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [2] )
	),
	New Column( "B",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [1] )
	)
);

//recursively find all unassigned names within the formula -- we can test these against the column names
get dangling names = Function( {formula},
	{Default Local},
	parse formula = Function( {expr, names = {}, assigns = {}},
		{Default Local},
		If( Contains( Head Name( expr ), "[" ) & Contains( Head Name( expr ), "]" ), // deal with the exceptional case where JMP doesn't arg-parse out this pattern...
			items = Words( Head Name( expr ), "[" );
			Summation( i = 1, N Items( items ),
				Try(
					item = Word( 1, items[i], "]" );
					If( Is Missing( Num( item ) ),
						{n, a} = Recurse( Parse( item ) );
						Insert Into( names, n );
						Insert Into( assigns, a );
					)
				);
				0
			)
		,
			If( Head Name( expr ) == "Assign", // if a variable is assigned within the formula then we don't care to check it (the formula shouldn't be direclty affecting other columns, it bad practice!)
				Insert Into( assigns, Char( Arg( expr, 1 ) ) )
			,
				Head Name( expr ) != Char( Name Expr( expr ) ), // Need to recursively parse here
				Summation( i = 1, N Arg( expr ),
					{n, a} = Recurse( Arg( expr, i ) );
					Insert Into( names, n );
					Insert Into( assigns, a );
					0
				);
			,
				Insert Into( names, Char( Name Expr( expr ) ) ) // found a name
			);
		);
		Eval List( {Associative Array( names ) << Get Keys, Associative Array( assigns ) << Get Keys} )
	);

	{names, assigns} = parse formula( Name Expr( formula ) );
	names = Associative Array( names );
	assigns = Associative Array( assigns );
	names << Remove( assigns );
	names = names << Get Keys;
	names
);

formulas = {};
Insert Into( formulas, Expr( :Name("C") - :Name("B") ) );
local var = [1 => 3, 2 => 4];
Insert Into( formulas, Eval Expr( 
		var = Expr( local var );
		var[:C][:F] + :D
	);
);
Insert Into( formulas, Expr( :A + :B ) );
Insert Into( formulas, Expr( :A + :B - Sin( :D + Cos( :E - :Name("flamingo@fish" ) ) ) ) );

For( i = 1, i <= N Items( formulas ), i++,
	table columns = dt << Get Column Names( "String" );
	Write( "\!N" );
	Show( table columns );
	formula references = get dangling names( formulas[i] );
	refs = {};
	For( j = 1, j <= N Items( formula references ), j++,
		refs[j] = Contains( table columns, formula references[j] ) > 0
	);
	Show( formulas[i] );
	Show( formula references );
	Print( If( Sum( refs ) == N Items( formula references ), "OKAY", "NOT OKAY" ) );
	If( Sum( refs ) == N Items( formula references ), // The table contains all the necessary columns, okay to add the new column!
		Eval( Eval Expr(
			dt << New Column( "FORMULA", <<Formula( Expr( formulas[i] ) ) )
		) )
	)
)

Let me know if you have any questions!

Jordan,