Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
walk545
Level II

join multiple tables from list

using the script here i can get a list of tables from any folder. i would like to be able to join tables from the created list. i want the user to be able to choose which tables he/her can join with a preset of which columns to use to join.

 

 

Path = Pick Directory("Select Analytics Data Directory");
Files_All = Files in Directory(Path);
NFS_All = N Items(Files_All);
Files = {};
//Remove non-text files
For (f=1, f < NFS_All+1, f++,
If(Contains(Files_All[f],".txt") > 0,
Insert Into(Files,Files_All[f]);
);
);

//Prompt to select individual files
List_NPr = {};
List_Pr = Files;
PromptF = New Window("File Selection", << Modal,
V List Box(
Text Box ("Select Desired Files"),
Text Box(" "),
H List Box(
V List Box(
Text Box("Files to Process:"),
Pr = List Box(List_Pr, width(350), nlines(30)),
Button Box(">>>> Remove Selected >>>>",
List_NPr = concat(List_NPr, Pr << Get Selected);
List_Pr = remove(List_Pr, Pr << Get Selected Indices);
Pr << Set Items(List_Pr);
NPr << Set Items(List_NPr);
)),
Text Box(" "),
V List Box(
Text Box("Files Excluded:"),
NPr = List Box(List_NPr, width(350), nlines(30)),
Button Box("<<<< Add Selected <<<<",
List_Pr = concat(List_Pr, NPr << Get Selected);
List_NPr = remove(List_NPr, NPr << Get Selected Indices);
Pr << Set Items(List_Pr);
NPr << Set Items(List_NPr);
)))));
wait(0);
Files = List_Pr;
NFS = N Items(Files);
);

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
gzmorgan0
Super User

Re: join multiple tables from list

So what is your question? You have an extraneous right parentheses in the last line of your script and you are not capturing the final list.

 

Here is your JSL modified.  FList contains the user selected files.   The last line shows the final selections.  Now you have to script the joins.

 

Path = Pick Directory( "Select Analytics Data Directory" );
Files_All = Files In Directory( Path );
NFS_All = N Items( Files_All );
Files = {};
//Remove non-text files
For( f = 1, f < NFS_All + 1, f++,
	If( Contains( Files_All[f], ".txt" ) > 0,
		Insert Into( Files, Files_All[f] )
	)
);

//Prompt to select individual files
List_NPr = {};
List_Pr = Files;
PromptF = New Window( "File Selection",
	<<Modal, ,
	V List Box(
		Text Box( "Select Desired Files" ),
		Text Box( " " ),
		H List Box(
			V List Box(
				Text Box( "Files to Process:" ),
				Pr = List Box( List_Pr, width( 350 ), nlines( 30 ) ),
				Button Box( ">>>> Remove Selected >>>>",
					List_NPr = Concat( List_NPr, Pr << Get Selected );
					List_Pr = Remove( List_Pr, Pr << Get Selected Indices );
					Pr << Set Items( List_Pr );
					NPr << Set Items( List_NPr );
					FList = List_Pr;
				)
			),
			Text Box( " " ),
			V List Box(
				Text Box( "Files Excluded:" ),
				NPr = List Box( List_NPr, width( 350 ), nlines( 30 ) ),
				Button Box( "<<<< Add Selected <<<<",
					List_Pr = Concat( List_Pr, NPr << Get Selected );
					List_NPr = Remove( List_NPr, NPr << Get Selected Indices );
					Pr << Set Items( List_Pr );
					NPr << Set Items( List_NPr );
					FList = List_Pr;
				)
			)
		)
	)
);
Wait( 0 );
Files = List_Pr;
NFS = N Items( Files );
show(FList);

View solution in original post

Highlighted
walk545
Level II

Re: join multiple tables from list

thank you!

i actually figured it out.

here is my saloution if anyone would like it.

Clear Globals();
Clear Symbols();
Main Menu("Minimize All");

//Set text export preferences
//Preferences(Export Settings(Export Table Headers));
//Preferences(Export Settings(End of Field(Tab)));

//Initial prompt to select split level
Prompt = New Window ("Output Selection", <<Modal,
	Text Box ("Welcome"),
	Text Box (""),
	Text Box (""),
	V List Box(
	H List Box(
	Panel Box("Pick Join Paramaters           ",
	A  = Radio Box({"Lot, Wafer ID","Lot, date","Entity, Lot"})
	),
	)
	),
	V List Box(Text Box (" 


	"),
	Button Box("OK",
	Join = A << get;
	))
	);
wait(0);

Path = Pick Directory("Select Analytics Data Directory");
Files_All = Files in Directory(Path);
NFS_All = N Items(Files_All);
Files = {};

//Remove non-text files
For (f=1, f < NFS_All+1, f++,
If(Contains(Files_All[f],".csv") > 0, 
Insert Into(Files,Files_All[f]);
);
);


//Prompt to select individual files
List_NPr = {};
List_Pr = Files;
PromptF = New Window("File Selection", << Modal,
V List Box(
Text Box ("Select Desired Files"),
Text Box(" "),
H List Box(
V List Box(
Text Box("Files to join:"),
Pr = List Box(List_Pr, width(350), nlines(30)),
Button Box(">>>> Remove Selected >>>>",
List_NPr = concat(List_NPr, Pr << Get Selected);
List_Pr = remove(List_Pr, Pr << Get Selected Indices);
Pr << Set Items(List_Pr); 
NPr << Set Items(List_NPr); 
)),
Text Box("     "),
V List Box(
Text Box("Files Excluded:"),
NPr = List Box(List_NPr, width(350), nlines(30)),
Button Box("<<<< Add Selected <<<<",
List_Pr = concat(List_Pr, NPr << Get Selected);
List_NPr = remove(List_NPr, NPr << Get Selected Indices);
Pr << Set Items(List_Pr); 
NPr << Set Items(List_NPr); 
)))));
wait(0);
Files = List_Pr;
NFS = N Items(Files);


// table that others join
Fullpath = Path||Files[1];

DT_old = Open( Fullpath,private);
DT_old << Minimize Window;

//loop to join other tables
For (a=2, a < NFS+1, a++,

Fullpath = Path||Files[a];

DT_new = Open( Fullpath,private);
DT_new << Minimize Window;


    join_name = "Limits" || char(a);

    dt_old << join( with(data table(dt_new)),
        Merge Same Name Columns,
        By Matching Columns(

            :A = :A,

            :B = :B,
            
              :C = :C,

        ),

        Drop multiples( 1, 0 ),

        Name( "Include non-matches" )(1, 1),

        Output Table( join_name )

    );

    close(dt_old, nosave);

   

    dt_old = data table(join_name);

);

View solution in original post

3 REPLIES 3
Highlighted
gzmorgan0
Super User

Re: join multiple tables from list

So what is your question? You have an extraneous right parentheses in the last line of your script and you are not capturing the final list.

 

Here is your JSL modified.  FList contains the user selected files.   The last line shows the final selections.  Now you have to script the joins.

 

Path = Pick Directory( "Select Analytics Data Directory" );
Files_All = Files In Directory( Path );
NFS_All = N Items( Files_All );
Files = {};
//Remove non-text files
For( f = 1, f < NFS_All + 1, f++,
	If( Contains( Files_All[f], ".txt" ) > 0,
		Insert Into( Files, Files_All[f] )
	)
);

//Prompt to select individual files
List_NPr = {};
List_Pr = Files;
PromptF = New Window( "File Selection",
	<<Modal, ,
	V List Box(
		Text Box( "Select Desired Files" ),
		Text Box( " " ),
		H List Box(
			V List Box(
				Text Box( "Files to Process:" ),
				Pr = List Box( List_Pr, width( 350 ), nlines( 30 ) ),
				Button Box( ">>>> Remove Selected >>>>",
					List_NPr = Concat( List_NPr, Pr << Get Selected );
					List_Pr = Remove( List_Pr, Pr << Get Selected Indices );
					Pr << Set Items( List_Pr );
					NPr << Set Items( List_NPr );
					FList = List_Pr;
				)
			),
			Text Box( " " ),
			V List Box(
				Text Box( "Files Excluded:" ),
				NPr = List Box( List_NPr, width( 350 ), nlines( 30 ) ),
				Button Box( "<<<< Add Selected <<<<",
					List_Pr = Concat( List_Pr, NPr << Get Selected );
					List_NPr = Remove( List_NPr, NPr << Get Selected Indices );
					Pr << Set Items( List_Pr );
					NPr << Set Items( List_NPr );
					FList = List_Pr;
				)
			)
		)
	)
);
Wait( 0 );
Files = List_Pr;
NFS = N Items( Files );
show(FList);

View solution in original post

Highlighted
walk545
Level II

Re: join multiple tables from list

thank you!

i actually figured it out.

here is my saloution if anyone would like it.

Clear Globals();
Clear Symbols();
Main Menu("Minimize All");

//Set text export preferences
//Preferences(Export Settings(Export Table Headers));
//Preferences(Export Settings(End of Field(Tab)));

//Initial prompt to select split level
Prompt = New Window ("Output Selection", <<Modal,
	Text Box ("Welcome"),
	Text Box (""),
	Text Box (""),
	V List Box(
	H List Box(
	Panel Box("Pick Join Paramaters           ",
	A  = Radio Box({"Lot, Wafer ID","Lot, date","Entity, Lot"})
	),
	)
	),
	V List Box(Text Box (" 


	"),
	Button Box("OK",
	Join = A << get;
	))
	);
wait(0);

Path = Pick Directory("Select Analytics Data Directory");
Files_All = Files in Directory(Path);
NFS_All = N Items(Files_All);
Files = {};

//Remove non-text files
For (f=1, f < NFS_All+1, f++,
If(Contains(Files_All[f],".csv") > 0, 
Insert Into(Files,Files_All[f]);
);
);


//Prompt to select individual files
List_NPr = {};
List_Pr = Files;
PromptF = New Window("File Selection", << Modal,
V List Box(
Text Box ("Select Desired Files"),
Text Box(" "),
H List Box(
V List Box(
Text Box("Files to join:"),
Pr = List Box(List_Pr, width(350), nlines(30)),
Button Box(">>>> Remove Selected >>>>",
List_NPr = concat(List_NPr, Pr << Get Selected);
List_Pr = remove(List_Pr, Pr << Get Selected Indices);
Pr << Set Items(List_Pr); 
NPr << Set Items(List_NPr); 
)),
Text Box("     "),
V List Box(
Text Box("Files Excluded:"),
NPr = List Box(List_NPr, width(350), nlines(30)),
Button Box("<<<< Add Selected <<<<",
List_Pr = concat(List_Pr, NPr << Get Selected);
List_NPr = remove(List_NPr, NPr << Get Selected Indices);
Pr << Set Items(List_Pr); 
NPr << Set Items(List_NPr); 
)))));
wait(0);
Files = List_Pr;
NFS = N Items(Files);


// table that others join
Fullpath = Path||Files[1];

DT_old = Open( Fullpath,private);
DT_old << Minimize Window;

//loop to join other tables
For (a=2, a < NFS+1, a++,

Fullpath = Path||Files[a];

DT_new = Open( Fullpath,private);
DT_new << Minimize Window;


    join_name = "Limits" || char(a);

    dt_old << join( with(data table(dt_new)),
        Merge Same Name Columns,
        By Matching Columns(

            :A = :A,

            :B = :B,
            
              :C = :C,

        ),

        Drop multiples( 1, 0 ),

        Name( "Include non-matches" )(1, 1),

        Output Table( join_name )

    );

    close(dt_old, nosave);

   

    dt_old = data table(join_name);

);

View solution in original post

Highlighted
txnelson
Super User

Re: join multiple tables from list

Please mark your solution as a "Solution".  I would also mark Ian's as a solution too.

Jim
Article Labels

    There are no labels assigned to this post.