Pick File () within SQL query?

Question

Hi all, First time posting so go easy on me ;) I would like to join two tables together inside JMP. The first is a data table outputted from a spectrometer, the other is a reference table. The joining keys for the tables don't match exactly so I have added maximum and minimum key columns into the reference table to use in conditions ((if (key > min key) and (key < max key) join other reference table columns to this row). I have previously used the JMP Query builder to join these together and it works quite well. However I am now wondering if it is possible to integrate the pick file object into this query. Essentially I would like the user to: * Run the query * Get a pop-up about which file they would like to analyse * The query would then run, matching any keys to the reference table where it falls within the minkey - maxkey region. * The user then gets the input table back, with additional columns where the query has been able to make the join. I've been mucking around for a couple days and have had no joy. The script below asks for the pick file input, only returns the table, it doesn't attempt to make a join at all. Any help/guidance is appreciated. New SQL Query( Version( 130 ), Connection( "JMP" ), JMP Tables( ["Reference Table" => "ReferenceTablePath", "Input Data" => Open( Pick File( "Select JMP File", "InputData Directory Path", {"All Files|*"}, 1, 0, "newJmpFile.jmp"), columns( New Column( "Key", Numeric, "Continuous", Format( "Best", 12 ) ), New Column( "Intensity", Numeric, "Continuous", Format( "Best", 12 ) ) ), Import Settings( End Of Line( CRLF, CR, LF ), End Of Field( Tab, CSV( 0 ) ), Strip Quotes( 1 ), Use Apostrophe as Quotation Mark( 0 ), Use Regional Settings( 0 ), Scan Whole File( 1 ), Treat empty columns as numeric( 0 ), CompressNumericColumns( 0 ), CompressCharacterColumns( 0 ), CompressAllowListCheck( 0 ), Labels( 0 ), Column Names Start( 1 ), Data Starts( 25 ), Lines To Read( "All" ), Year Rule( "20xx" )))] ), QueryName( "SQLQuery2" ), Select( Column( "Key", "t1" ), Column( "Intensity", "t1" ), Column( "Value1", "t2" ), Column( "Value2", "t2" ) ), From( Table( "Input Data", Alias( "t1" ) ), Table( "Reference Table", Alias( "t2" ), Join( Type( Left Outer ), GE( Column( "Key", "t1" ), Column( "MinKey", "t2" ) ) & LE( Column( "Key", "t1" ), Column( "MaxKey", "t2" ) ) ) ) ) ) << Run

Jackie_ · Answer

Hi @SamEllick I am having issues with Pick File syntax in SQL query. Can you assist me ? New SQL Query( Version( 130 ), Connection( "JMP" ), JMP Tables( ["Limits" => "Source(Open( \!"C:\Jay\JMP\JMP Scripts\Python Scripts\JMP scripts\New folder\New folder\SQL 2\Limits.xlsx\!", Worksheets( \!"Limits\!" ), Use for all sheets( 1 ), Concatenate Worksheets( 0 ), Create Concatenation Column( 0 ), Worksheet Settings( 1, Has Column Headers( 1 ), Number of Rows in Headers( 1 ), Headers Start on Row( 1 ), Data Starts on Row( 2 ), Data Starts on Column( 1 ), Data Ends on Row( 0 ), Data Ends on Column( 0 ), Replicated Spanned Rows( 1 ), Replicated Spanned Headers( 0 ), Suppress Hidden Rows( 1 ), Suppress Hidden Columns( 1 ), Suppress Empty Columns( 1 ), Treat as Hierarchy( 0 ), Multiple Series Stack( 0 ), Import Cell Colors( 0 ), Limit Column Detect( 0 ), Column Separator String( \!"-\!" ) ) ))", "SummaryTable" => "\C:\Jay\JMP\JMP Scripts\Python Scripts\JMP scripts\New folder\New folder\SQL 2\SummaryTable.jmp" ] ), QueryName( "SQLQuery11" ), Select( Column( "Test Number", "t2" ), Column( "Test Name", "t2" ), Column( "Test Units", "t2" ), Column( "LL", "t2" ), Column( "UL", "t2" ), Column( "N", "t2" ), Column( "Failing Soft Bin", "t1" ) ), From( Table( "Limits", Alias( "t1" ) ), Table( "SummaryTable", Alias( "t2" ), Join( Type( Left Outer ), EQ( Column( "Test Number", "t2" ), Column( "Test Number", "t1" ) ) & EQ( Column( "Test Name", "t2" ), Column( "Test Name", "t1" ) ) & EQ( Column( "Test Units", "t2" ), Column( "Test Units", "t1" ) ) ) ) ) ) << Run I want to have two Pick file command in the SQL query. Any Suggestions?

SamEllick · Answer

Hi @cwillden

No worries, thanks for the idea, I managed to get it to work. I stored the pick file path in a variable and then called it inside the query (just like you suggested). When I tried this previously I used the open object as well (pick file -> open -> sql query), without scucess.

It seems you just need: pick file -> sql query.

Cheers

cwillden · Answer

Hi @SamEllick,

Have you tried doing the Pick File outside the query and just storing the path in a variable?  Or, pick the file and open it before initiating the query and referencing the open data table?

I have very little experience with Query Builder, but I felt bad that no one had helped you out.  Can you post some kind of sanitized version of the tables you're working with so we can try to figure it out in context?