Reference Table file name in SQL query

Jackie_ — Fri, 09 Jun 2023 23:26:44 GMT

I used JMP Query Builder to create the following SQL Query

Names Default To Here( 1 );
//Prompt to select and open an MS Excel file
dt1 = Open( Pick File( "Select Excel File", "$DOCUMENTS", {"Excel Files|csv;xlsx;xls", "All Files|*"}, 1, 0 ), Invisible );

//Prompt to select an open a JMP data table
dt2 = Open( Pick File( "Select JMP File", "$DOCUMENTS", {"JMP Files|jmp", "All Files|*"}, 1, 0 ), Invisible );

//Perform a left outer join on the tables using the "Name" Column
New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables(
		["1st_file" =>
		"Source(Open( \!"dt1\!",
	Worksheets( \!"worksheet\!" ),
	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( 1 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( \!"-\!" )
	)
))",
		"2nd_file" => "Source(Open( \!"dt2\!"))"]
	),
	QueryName( "Data_Blending" ),
	Select(
		Column( "Name", "t2" ),
		Column( "Enable", "t2" ),
		Column( "Type", "t2" ),
		Column( "Phase", "t2" ),
		Column( "Values", "t3" )
	),
	From(
		Table( "2nd_file", Alias( "t2" ) ),
		Table(
			"1st_file",
			Alias( "t3" ),
			Join(
				Type( Left Outer ),
				EQ( Column( "Name", "t2" ), Column( "Name", "t3" ) )
			)
		)
	)
) << Run;

//Close source files without saving
Close(dt1, "NoSave");
Close(dt2, "NoSave")

The above script will perform Left Join.
More specifically it will copy the "Values" Column from 2nd file into 1st file and creates Data_Blending JMP Table.

The above script works only if the file name is entered in the Query.

What if I want to run the same script on different files without changing the Reference Table name ?

Let's my 2nd file name remains constant and I want to perform similar operations on different files.

I have another excel file "3rd_file" and I don't want to change the name in the Query. Is there any Syntax which can copy the file name in the red circle part of the following image?

Any help/guidance is appreciated.

Re: Reference Table file name in SQL query

stan_koprowski — Wed, 25 Mar 2020 16:24:01 GMT

Hi @Jackie_ ,

For others following along, one of the best teachers for writing JSL using JMP is to first perform the steps interactively and then ask JMP to write the script.

I'd like to suggest watching some instructional videos using JMP Query Builder from our online series Mastering JMP.

Additional tutorials on data wrangling using JMP Query Builder from our Discovery Summit Series.

To extend the join amongst additional tables the general process is the same as previously shown with two tables.

cheers,

Stan

topic Re: Reference Table file name in SQL query in Discussions

Reference Table file name in SQL query

Re: Reference Table file name in SQL query