890175_en-US

liliconcom

Quickly show Data View with subset of rows that match regex

I'm looking for a JSL script that does the following:

 

1. Gets the currently selected columns in the active data table.

2. Takes the first selected column.

3. Prompts the user to enter a regular expression.

4. Selects only the rows in the table where the value in that column matches the regex.

5. Opens a Data View window showing only those matching rows.

 

Is this possible with JSL?

Automation and ScriptingData AccessRe: Quickly show Data View with subset of rows that match regex

It is possible. What have you tried? 

 

This is very basic example missing all checks which could (and most likely should) be implemented

Names Default To Here(1); 

dt = Current Data Table();

col = (dt << Get Selected Columns("String"))[1]; // I suggest dropping non-character columns

run_expr = Expr(
	rgx_pattern = teb << Get Text; // You could check if the pattern is valid regex
	dt << Select Where(!Is Missing(Regex(Column(dt, col)[Row()], rgx_pattern)));
	// add check if any rows have been selected
	dt << Data View; // If linked subset is fine, that is most likely better as it can avoid using select where
	dt << Clear Select;
	wait(0);
);

nw = New Window("",
	H List Box(
		Lineup Box(N Col(2),
			Text Box("Enter regex pattern"),
			teb = Text Edit Box("")
		),
		Lineup Box(N Col(1),
			Button Box("OK",
				run_expr
			),
			Button Box("Cancel")
		)
	)
);

Write();

Re: Quickly show Data View with subset of rows that match regex

That works, thanks.

 

To be honest, I didn't include any code because my attempt wasn't very close, and exposes some fundamental misunderstandings about JSL that probably come from me learning GUI scripting from AppleScript and Matlab.

 

I would've gotten the list of selected column names, picked the first one, but then I expected some kind of "display dialog" function which returns a string, but that apparently doesn't exist, and forces you into this new-window/list-box/button-expression syntax, which was fairly unintuitive to me, but makes more sense now that I see what it's doing.  I tried ChatGPT, but it created long scripts that didn't run at all.

 

A few questions about your solution, if you have time:

 

1. Why doesn't the "Cancel" button do anything?  I presume because you have no 2nd argument to its Button Box() call, but what expression would close the current window?  Close()?  I tried "Button Box("Cancel", Close())" but it errors out with an error I don't understand.  I mimicked your approach and tried "Close Window" which seems to work, but I expect there's a cleaner way to do it.
2. Your "Get Selected Columns()" call has "String" as an argument, but the documentation shows that function takes no arguments.  What does "String" mean here, and how did you know it would be accepted or what it does?
3. I'd like to start typing immediately after running the script, but the focus isn't in the right place.  I tried "teb << SetFocus;" after the "nw=..." expression, but JMP says text edit boxes don't understand that message.
4. Is it possible to make Regex be case insensitive, and if so, how?  (I tried adding "" to the Regex arguments per the documentation, but it errors out.)
5. Is it possible to make return/enter trigger the "Ok" script with the teb box focused?
6. If the user has no selected columns, I'd like to use the first character column, but after about 20 different attempts I gave up trying to figure out which column that is.
7. I would prefer the data view subset be linked, does that change the approach?

 

Here's what I have so far:

 

Names Default To Here(1); 

run_expr = Expr(
	dt = Current Data Table();
	selCols = dt << Get Selected Columns("String");
	if(N Items(selCols)==0, selCols = dt << get column names("String") );
	col = selCols[1];
	rgx_pattern = teb << Get Text;
	dt << Select Where(!Is Missing(Regex(Column(dt, col)[Row()], rgx_pattern)));
	dt << Data View; // I prefer a linked subset, what changes here?
	dt << Clear Select;
	wait(0);
	nw << Close Window;
);

close_expr = Expr(nw << Close Window);

nw = New Window("",
	H List Box(
		Lineup Box(N Col(2),
			Text Box("Enter regex pattern"),
			teb = Text Edit Box("", <<SET width=""></SET>

I'd love to open Big Class, run the script, then type "ob.+t" and have it bring up a Data View of just the two Roberts.  Right now it's close -- after running the script, I have to press tab, then OB.+T, then enter twice.

Re: Quickly show Data View with subset of rows that match regex

1. I created as minimal example as I could, so Cancel doesn't contain expression to close the window
2. I just tried if "String" would work like it does with << get column names() and it does. Test it out without the "String" value and see what you get (references instead of strings). In my opinion, working with the column references is a nightmare so I always use strings of column names and convert them as needed. But it really is up to you if you prefer using references (or whatever that colon syntax is)
3. I think you are stuck with tab unless you wish to use complicated workaroundsAllow SetFocus to be used with interactive display elements (TextEditBox, Button...) 
4. You could Lowercase() user input and wrap the column reference with Lowercase(). Or, I think you could add the default capturing group to Regex() when using INGORECASE: Regex("ABCD", "d", "\0", IGNORECASE)
5. You could try of << Set Function would fit your needs. It will execute the script on commit and enter should trigger that (as does clicking outside of the text edit box). 

6. << Get Column Names(Character, String)[1]

7. r = dt << Get Rows Where(!Is Missing(Regex(Column(dt, col)[Row()], rgx_pattern)));
   dt_subset = dt << Subset(Rows(dt), Selected Columns(0), Link to original data table(1));

There are plenty of improvements which could be done but what those could be depends on the real use case.

 

Edit: Added link to original data table(1) to subset

 

Re: Quickly show Data View with subset of rows that match regex

Excellent, thank you!

 

Just one more question:  If multiple columns are selected, I'd like to perform the regex on the column values concatenated together as strings, and if no columns are selected, I'd like to do the same thing on *all* columns.  This effectively mimics the behavior of egrep on the table.  Is that reasonably doable?  See commented line below for my initial attempt at this, but it doesn't pull just the values from the selected columns, so it's not there yet (and it doesn't work).

 

Names Default To Here( 1 ); 

run_expr = Expr(
	dt = Current Data Table();
	selCols = dt << Get Selected Columns( "String" );
	If( N Items( selCols ) == 0,
		selCols = dt << Get Column Names( "String" )
	);
	col = selCols[1];
	rgx_pattern = teb << Get Text;
	dt << Select Where( !Is Missing( Regex( Column( dt, col )[Row()], rgx_pattern, "1", IGNORECASE ) ) );
//	dt << Select Where( !Is Missing( Regex( Concat Items( dt << get Rows(Row())," "), rgx_pattern, "1", IGNORECASE ) ) );
	dt << Data View;
	Wait( 0 );
);

close_expr = Expr( nw << Close Window; Wait( 0 ); );

nw = New Window( "Table Egrep",
	H List Box(
		lb1 = Lineup Box( N Col( 2 ),
			Text Box( "Select column to Enter regex pattern" ),
			teb = Text Edit Box( "", Set Script( run_expr ), <<SET width=""></SET>

FYI, the "Set Script" above handles the immediate execute on "enter", which works great.  The only missing desired behavior is the one above, and the need to hit "tab" to get the focus into the text edit box.

Re: Quickly show Data View with subset of rows that match regex

It might start getting slow at some point and you should add a check that only character columns are selected, but you can build expression to create the concat

 

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
dt << Select Columns({:name, :sex});
rgx_patter = "EF";

cols = dt << Get Selected Columns("String");

conc_expr = Expr(Concat());

For Each({colname}, cols,
	Insert Into(conc_expr, Name Expr(As Column(dt, colname)));
);

regex_expr = Substitute(
	Expr(!Is Missing(Regex(_conc_expr_, _rgx_pattern_, "\0", IGNORECASE))),
	Expr(_conc_expr_), Name Expr(conc_expr),
	Expr(_rgx_pattern_), rgx_pattern
);

rows_of_interest = dt << Get Rows Where(regex_expr);

 

Re: Quickly show Data View with subset of rows that match regex

You can also force the focus to be in the window by using Modal Window but those can be quite bad for user experience.

Re: Quickly show Data View with subset of rows that match regex

Just when I thought I was starting to understand…

 

The documentation says that Expr() "returns its argument unevaluated"—which honestly means nothing to me. It sounds like it just gives back whatever you pass in.

 

In your earlier code, it looked like a subroutine, which made sense at the time. But now I’m seeing Expr(Concat()), and I can’t figure out what’s actually being concatenated. Then later, you Insert Into() that Expr() object—but how does JMP know where to insert the values? Even ChatGPT isn’t helping me make sense of this.

 

It feels like trying to recreate a magician’s trick just by copying the hand movements—frustrating, especially since the documentation for Get Row() seems to promise exactly the behavior I’m trying to get.

 

None of this is your fault, of course, and I really appreciate your help. I think what I have now will work well enough. Thanks!

Re: Quickly show Data View with subset of rows that match regex

Nothing is being concatenated here Expr(Concat()). In this case you can see this as a collector for arguments which will be concatenated after you have finished building the expression. You could also build it with a list and then substitute the list with concat

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");

val1 = "name";
val2 = "sex";

c = {};

Insert Into(c, Name Expr(As Column(dt, val1)));
Insert Into(c, Name Expr(As Column(dt, val2)));

show(c);

Substitute Into(c, Expr(List()), Expr(Concat()));

Show(c);

// Example use
Eval(EvalExpr(
	dt << New Column("Concat", Character, Nominal, Formula(
		Expr(Name Expr(c))
	));	
));

Re: Quickly show Data View with subset of rows that match regex

Ok, I think I've figured this out -- this script is basically the tabular equivalent of the unix/linux/mac "egrep" command, i.e. select/show just the rows that match a user-entered regular expression (search string):

 

Names Default To Here( 1 ); 

If(
	ex = New Window( "Enter search regex",
		<<MODAL></MODAL>

All of the buttons work correctly, and it's reasonably fast even with many thousands of rows.  Personally, I made an Addin of it and tied it to cmd-shift-e (egrep) and now use it all the time.  Of course I'm open to suggestions on how to improve it.  Hope this helps someone!

 

(And big thanks to @jthi for his patience and insight!)

Re: Quickly show Data View with subset of rows that match regex

Looks good! I would make few small changes:

• Before opening the New Window check that there is a table open and if there isn't one, run stop();
• If there are no matches, the last row will throw an error. You could either check selected row count and use stop() if it is 0 or show information to the user that there was no matches
• << Get Rows Where is mos likely faster than << Select Where
  • Some additional speed-up can most likely be gained by evaluating the column reference
• << On Validate could also be used if you wish to let user input new regex if the first doesn't have any matches

 

View more...

Names Default To Here(1); 

If(N Table() == 0,
	stop(); // display error message to user if necessary
);

dt = Current Data Table();

nw = New Window("Enter search regex",
	<<MODAL> 0,
	col = selCols[1];
,
	col = Column(dt, 1) << get name;
);

selrows = Eval(EvalExpr(
	dt << Get Rows Where(!Is Missing(Regex(Expr(NameExpr(As Column(dt, col))), inputRegex, "1", IGNORECASE)));
));

If(N Rows(selrows) == 0,
	stop(); // display error message if necessary, this could also be done already within modal window (<< On Validate), if you wish to let user perform new search
);

dt2 = dt << Subset(Rows(selrows), Selected Columns(0), Link to original data table(1), Output Table(""));
// dt2 << Select Rows(1::NRows(dt2)); // to select rows if necessary</MODAL>