Can you search column formulas?

[#1]

I have a data table with a couple hundred columns. I would like to be able to search through all the column formulas to see if a particular column is used in any of the column formulas. Is this possible?

[#2 · Accepted solution]

Yes......

Names Default To Here( 1 );

dt = 
// Open Data Table: Big Class.jmp
// → Data Table( "Big Class" )
Open( "/C:/Program Files/JMP/JMPPRO/19/Samples/Data/Big Class.jmp" );

dt << new column("Ratio", formula(:height/:weight));

theFormula = Char(dt:Ratio << get formula);

If( contains(theFormula, "height"), show("found it"));

[#3]

Jim,

Thanks for this. Since I am really poor at scripting at this point, could you show me how I can have your algorithm loop through all the columns in a data table and output a list of columns that contain the target value?

Jarmo,

Unfortunately, I don't have JMP Pro. For some reason, the error message I get when I try to delete the column does not list the column containing the formula. I don't know why. I also thought that the data dictionary might be able to help but I couldn't see how once I looked at it. Thanks for the assistance though.

[#4]

I also shared one more option which isn't dependent on JMP Pro

You can select the formula columns, select copy columns, open new script (ctrl + t) and paste there. Then search in script editor.

[#5 · Accepted solution]

Ohhhh... That's cool. I didn't know you could paste a column into the script editor.

I think that will work.

Thanks!

[#6]

If you have access to JMP Pro you could do this with Formula Depot.

You can select the formula columns, select copy columns, open new script (ctrl + t) and paste there. Then search in script editor.

You could also try removing that column and if it is being used, JMP will display warning like

Edit:

There might be also some way in JMP18 utilizing Columns Manager but I could figure one out. Data Dictionary almost can do it but it stores the formulas as images so they cannot be searched easily

And of course there is plenty of pure scripting options

[#7 · Accepted solution]

Similar to @jthi's suggestion to copy the script of each column, you can copy the script of the entire table without the data using the Red Triangle (at the top left of the table) > Copy Table Script (no data).

The script will contain the jsl to recreate your table, which will include all your column formulas. Paste that script into a script window, and search from there. 

I hope this helps!

@julian 

[#8]

@julian maybe this type of searches could be implemented to Column Manager in some capability? So we could also search for the values within Column Properties instead of just filtering down to those which have specific properties. At least Formula, Notes and Short/Long column names are few column properties which could possibly benefit if we could search from.

[#9]

I like it—Columns Manager feels like a natural place for that. I was also thinking of how it might work within the ordinary Find dialog if there were another checkbox to offer “Find in Column Properties.”

[#10]

I started with  @julian 's idea to use the table script and added some functions to

- search for columns in the formulas and

- to display the search results:

//Open( "$SAMPLE_DATA/Ship Damage.jmp" );
// get all column formulas via the table script 
dt = Current Data Table();
dt << copy table script( "No Data" );
script = Parse( Get Clipboard() );

//convert to List for easier indexing
Substitute Into( script, Expr( New Table() ), Expr( {} ) );

// just keep the columns
allCols = Filter Each( {col}, script, Head( col ) == Expr( New Column() ) );

// convert column info into lists
ColList = Transform Each( {col}, allCols, Substitute( Name Expr( col ), Expr( New Column() ), Expr( {} ) ) );
allColNames = Transform Each( {col}, ColList, tmp = Arg( col, 1 ); If (is string(tmp), tmp, tmp["en"]) );

// keep columns with formulas
ColsWithFormulas = Filter Each( {col}, ColList,Try(col["Formula"];	1	,	0	));

ColNames = Transform Each( {col}, ColsWithFormulas, tmp = Arg( col, 1 ); If (is string(tmp), tmp, tmp["en"]) );
ColFormulas = Transform Each( {col}, ColsWithFormulas, col["Formula"] );

// helper function to search for column names in formulas
ContainsExpr = Function( {expression, pattern},
	tmp = Substitute( Name Expr( expression ), Name Expr( pattern ), Expr( . ) );
	Not( Name Expr( tmp ) == Name Expr( expression ) );
);
 
summary = New Table("Formuala Overview",
	New Column( "column Formula", Character ),
	New Column( "used cols", Character ), 	
);
wait(0);

// fill the summary tale with all combinations of formulas and the used columns

Current Data Table( dt );  // make dt the current data table, otherwise <As column> won't work !
For Each( {myFormula, idx1}, ColFormulas,
	For Each( {col}, allColNames,
		formulaCol = ColNames[idx1];
		dt:formulaCol << Set Header Text Color( "Red" );
		If( ContainsExpr( Name Expr( myFormula ), Name Expr( As Column( col ) ) ),
			Eval( Eval Expr( summary << add row( {"column Formula"n =Expr(formulaCol ), "used cols"n = Expr( col )} ) ) );
			Column(col) << Set Header Background Color( {102, 255, 140} );
		)
	)
);
 
// show a tabulate for easy look up
summary << Tabulate(
	Show Control Panel( 0 ),
	Add Table( Row Table( Grouping Columns( :column Formula, :used cols ) ) ),
	Local Data Filter( Mode( Show( 0 ) ), Add Filter( columns( :column Formula, :used cols ) ) )
);