cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Register for our Discovery Summit 2024 conference, Oct. 21-24, where you’ll learn, connect, and be inspired.
Choose Language Hide Translation Bar
scott1588
Level IV

Can you search column formulas?

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?

3 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: Can you search column formulas?

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

View solution in original post

scott1588
Level IV

Re: Can you search column formulas?

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

 

I think that will work.

 

Thanks!

View solution in original post

julian
Community Manager Community Manager

Re: Can you search column formulas?

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

 

julian_0-1721339096365.png

 

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 

 

View solution in original post

11 REPLIES 11
txnelson
Super User

Re: Can you search column formulas?

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"));
Jim
scott1588
Level IV

Re: Can you search column formulas?

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.

jthi
Super User

Re: Can you search column formulas?

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.

 

jthi_0-1721329913297.png

jthi_2-1721329951299.png

 

 

-Jarmo
scott1588
Level IV

Re: Can you search column formulas?

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

 

I think that will work.

 

Thanks!

jthi
Super User

Re: Can you search column formulas?

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

jthi_1-1721326886768.png

 

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

jthi_0-1721326874531.png

 

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

jthi_0-1721327241713.png

 

And of course there is plenty of pure scripting options

-Jarmo
julian
Community Manager Community Manager

Re: Can you search column formulas?

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

 

julian_0-1721339096365.png

 

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 

 

jthi
Super User

Re: Can you search column formulas?

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

-Jarmo
julian
Community Manager Community Manager

Re: Can you search column formulas?

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.”
hogi
Level XII

Re: Can you search column formulas?

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