- JMP User Community
- :
- Discussions
- :
- Can you search column formulas?

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

Jul 18, 2024 02:13 PM
(1073 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

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

I think that will work.

Thanks!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

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!

11 REPLIES 11

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

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

-Jarmo

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

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

I think that will work.

Thanks!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

Created:
Jul 18, 2024 02:22 PM
| Last Modified: Jul 18, 2024 11:29 AM
(902 views)
| Posted in reply to message from scott1588 07-18-2024

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

-Jarmo

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

Created:
Jul 27, 2024 01:10 PM
| Last Modified: Aug 1, 2024 1:52 PM
(523 views)
| Posted in reply to message from julian 07-19-2024

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

- © 2024 JMP Statistical Discovery LLC. All Rights Reserved.
- Terms of Use
- Privacy Statement
- About JMP
- JMP Software
- JMP User Community
- Contact