cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
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
gzmorgan0
Super User (Alumni)

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

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
gzmorgan0
Super User (Alumni)

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);
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);

);

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