Community Manager Community Manager

JMP Journal from Advanced Mastering Accessing Databases

Chris Kirchberg chris.kirchberg​ uses this JMP Journal in his Advanced Mastering JMP: Accessing Databases webcast. Other resources for this webcast include:


After the April 28, 2018 webcast, one of our key developers, Eric Hill answered some questions that came up at the webcast but which Chris did not have time to address. Here are the Query Builder questions and Eric's responses:


Question: When in the initial Query Builder window where you can join tables, can you join between secondary tables?

Answer: Absolutely.  Any table in the query can be joined to any other. 


Question: Can I join two tables if key/column is numeric in one table and character in the other?

Answer: For some databases, join columns with mismatched types are coerced to be the same type, but others not.  If you have this situation, the  best thing to do is drop into custom SQL and cast the string to a number.  Though this StackOverflow article suggests that SQL will just do the conversions on your behalf.


Question: When one chooses to aggregate using Average, how is that done? For example, in the Movie Rental example, when we take average age, is that averaging across unique customers or looking at all the records within the movie information that defines a group? The concern is that if a customer is renting multiple times the same movie, they might get counted multiple times and thus the sample size would be inflated. I suspect this might be dependent on the DB type.

Answer: The database is going to average across whatever rows are in each group.  The best thing to do is to make the query you want without aggregating, making sure if you only want each customer to be present one time that that’s what you’ve got, and then apply the aggregation.  It may require custom SQL or doing one query that gets the rows you want and then a JMP query against the result that does the aggregation.


A question came up during the webcast about adding/using the command <include ("//link.jsl"); > to execute a referenced script(s) from the Post Query Script section of Query Builder.


The answer is yes, you can use the include command for reference an external jsl file and it will be executed. You do have to make sure that you use the absolute path and not a relative path.


Why would someone want to do this? To maintain scripts outside of the query builder/query builder script itself so one can make and maintain dashboards or other such views seperately.


Hope that helps people out there using Query Builder!



Article Labels
Article Tags