Hi @stan_koprowski ,
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.