843110_en-US

liliconcom

Recordings and files: Scripters Club on Data Access, JMP Roasting and Tips and Tricks

Video 1 Data Access from @Georg 

This video is currently being processed. Please try again in a few minutes.

(view in My Videos)

 

Comments: 

• Even though the documentation is still lacking and it can be a bit messy to use... I would still suggest you use it as it is the preferred method and will (and does) support connectors
• There is also queryResult "symbol" you can access on run complete which should be the reference to the resulting table https://www.jmp.com/support/help/en/18.1/#page/jmp/sql-functions.shtml
• Always use either <<< Run Foreground and do not rely on << Run which is determined by user preferences

Video 2: JMP Roasting fro, @jthi 

This video is currently being processed. Please try again in a few minutes.

(view in My Videos)

 

Comments: 

• I would recommend to look in scripting index, how the new window command works well in different modes, there are examples for most variants (modal, report, ...)
• Agree, there examples there. Not always as useful as I would prefer. Pretty much every time I use the NewWindow(), I want to do something for which there is no similar example. I have found really helpful examples for NewWindow() while searching the community.
• Please also add a possibility to add examples. There are very useful examples in the community. I think it doesn't make sense to flood the Scripting Index with all the examples. But it will help us a lot I we get the possibility to add useful examples to OUR scripting index.

• Here is the start of the wish list entry: https://community.jmp.com/t5/JMP-Wish-List/possibility-to-add-Examples-to-the-Scriptiing-Index/idi-p/842817

 

Video 3: Tips and Tricks from @jthi 

This video is currently being processed. Please try again in a few minutes.

(view in My Videos)

 

Post for suggestions: https://community.jmp.com/t5/JMP-Scripters-Club-Discussions/Collection-of-good-valuable-scripting-examples-the-scripters/m-p/842828#M215

 

Re: Recordings and files: Scripters Club on Data Access, JMP Roasting and Tips and Tricks

My material attached

Re: Recordings and files: Scripters Club on Data Access, JMP Roasting and Tips and Tricks

Bravo! It's amazing how these sessions continue being sources of immediately useful knowledge since their inception in 2023.

 

I am wondering whether there's an opinion on SQL inside the Open Database() JSL function? We are using an implementation of Google's BigQuery for data warehousing. Inside BigQuery, it is possible to develop and debug SQL queries that can be copy-pasted directly into Open Database() like shown below. The possibility to concatenate SQL keywords or timestamps into the SQL appears very useful too.

Names Default to Here(1);

start = "'2025-02-18 00:00:00'"; 
stop = "'2025-02-18 01:00:00'";

dt = Open Database( "DSN_name", "

	SELECT 
		time,
		tagname,
		value
	  
	FROM 
		`bq_project.dataset.table`

	WHERE 
		time BETWEEN TIMESTAMP("|| start ||") AND TIMESTAMP("|| stop ||")
	  
	AND tagname IN(
		'tag_1',
		'tag_2' 
		)"
);

 

Re: Recordings and files: Scripters Club on Data Access, JMP Roasting and Tips and Tricks

Use New SQL Query (I know it is quite horrible... but it is the preferred method) and utilize sql template and then evaluate it using Eval Insert would be my suggestion. Example below:

Names Default to Here(1);

MY_CONNECTOR = "ODBC:...";

start = "'2025-02-18 00:00:00'"; 
stop = "'2025-02-18 01:00:00'";

sql_template = "\[
SELECT 
	time,
	tagname,
	value  
FROM 
	`bq_project.dataset.table`
WHERE 
	time BETWEEN TIMESTAMP(¤start¤) AND TIMESTAMP(¤stop¤)
AND tagname IN(
	'tag_1',
	'tag_2' 
	)
]\";

sql_str = Eval Insert(sql_template, "¤");

// Write("\!N", sql_str);

obj = New SQL Query(
	Connection(MY_CONNECTOR),
	Custom SQL(sql_str),
	Query Name("mytable")
);
dt = obj << Run Foreground();

Write();

You can use similar template idea for other methods than New SQL Query (like the Open Database()).

 

I use special character "¤" in my Eval Insert() because the default "^" is commonly found for example from Regex and it will cause issues during evaluation.

 

Edit: Fixed few typos, improved language and added comment about "¤"

Re: Recordings and files: Scripters Club on Data Access, JMP Roasting and Tips and Tricks

Thank you @jthi for the very useful presentation! Inspired me to rewrite a dynamically created modal window to avoid using strings and to use your XPath method for extracting results.

 

I did find one thing where I couldn't avoid parsing strings though - I want to include XPath commands inside the modal window but it needs to be dynamically created.

 

I want to create a check box that controls whether a text edit box is enabled or not, however, I don't know how many text edit box/check box pairs there will be:

rc_list = Lineup Box( N Col( 3 ) );
rc_list << append( Text Box( "Current", <<SET font=""></SET>

Is there a way of doing this so I don't need to parse JSL strings? Or is there a better way to do this modal recode?

 

My complete code is here:

View more...

Names Default To Here( 1 );

// Function to do a simple in-place column recode. 
// Uses a modal dialog so can be used as part of a larger script or workflow.
modal_recode = Function( {dt, col},
	{default local}, 
	
	Summarize( dt, orig_vals = By( col ) );

	rc_list = Lineup Box( N Col( 3 ) );
	rc_list << append( Text Box( "Current", <<SET font=""></SET>

Re: Recordings and files: Scripters Club on Data Access, JMP Roasting and Tips and Tricks

I wouldn't use << XPath in this case but rather this << prev sib. I assume this does what you are looking for:

So instead of

Eval(
	Parse(
		"\[rc_list << append( check box( "", <<SET function=""></SET>

you could do something like this

rc_list << Append(Check Box("", << Set Function(function({this},
	(this << prev sib) << Enabled(this << get);
))));

Full code

View more...

Names Default To Here(1);

// Function to do a simple in-place column recode. 
// Uses a modal dialog so can be used as part of a larger script or workflow.
modal_recode = Function({dt, col},
	{default local}, 
	
	Summarize(dt, orig_vals = By(col));

	rc_list = Lineup Box(N Col(3));
	rc_list << append(Text Box("Current", <<SET font=""></SET>

If you really want to use XPath for this, you can dynamically calculate the index. You can for example get all the CheckBoxBoxes and then use Contains with this to get the index and then use that index

idx = Contains(rc_list << XPath("//CheckBoxBox"), this);

Re: Recordings and files: Scripters Club on Data Access, JMP Roasting and Tips and Tricks

Thank you @jthi! The prev sib approach works for me. I need to change the way I naturally think about how JMP windows are constructed and how they can be manipulated.

Re: Recordings and files: Scripters Club on Data Access, JMP Roasting and Tips and Tricks

I have changed my thinking many times and I still do from time to time. I think I generally use four different methods (annd combination(s) of these) depending on situation:

• XPath
  • If I want to collect results from multiple things on button press
• Collect the references to list/associative array
  • Sometimes it just is easy to collect the reference to an object
• << Set Function(function(this()))
  • Usually to control some actions, like in your case the enable(0/1). I don't usually update any variable values with this
• Reference evaluation
  • No specific examples, maybe when for some reason building the "path" with XPath or using this gets annoying and I still need the reference (sometimes in classes or functions which return new window)

Reference evaluation example

 

rc_list << append(teb = Text Edit Box(Char(orig_vals[i]), <<ENABLED></ENABLED>

 

 

Re: Recordings and files: Scripters Club on Data Access, JMP Roasting and Tips and Tricks

There's only so many kudos I can give for a single reply. Thanks, @jthi, really.