topic Re: join multiple tables from list in Discussions

join multiple tables from list

walk545 — Tue, 16 Apr 2019 06:40:49 GMT

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

Re: join multiple tables from list

gzmorgan0 — Tue, 16 Apr 2019 09:01:55 GMT

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

Re: join multiple tables from list

walk545 — Tue, 16 Apr 2019 13:38:46 GMT

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

);

Re: join multiple tables from list

txnelson — Tue, 16 Apr 2019 21:57:42 GMT

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