topic Concatenating/Merging/Updating Multiple Data Tables from a loop into one in Discussions

Concatenating/Merging/Updating Multiple Data Tables from a loop into one

tnodd — Sat, 10 Jun 2023 23:18:39 GMT

Hi,

I have the following problem:

a) I am generating a table (call it table dt) with mutiple columns. However I am interested to only look at specific pattern (say - "ABC@XYZ") in the col names along with two other columns (say - "LCOL", "WCOL"). I am generating that table. ( Calling that table dtB)

b) I use the table dtB, pulls its column and use a for loop to generate a summary table for each such column which matches the pattern in Col Name.

c) I have created an empty table list, dt_sum_all

d) Now my goal is to obtain all the summary values of the columns which match the pattern into one table along with LCOL & WCOL column. ( I am getting N tables for N such columns, if I do not use the new table, but that is not what I want - I want 1 table)

e) Also at the end once I have a single table, I want to rename the Cols ( usually it appends SUM at the beginning of each column), like SUM (ABC@XYZ_1AS) to ABC@XYZ_1AS . Since I have many columns, I want to do it in a loop or similar.

What I have till now is the following:

dt = currentdatatable();

Wait(1); 

colsd = dt << get column names( string );
pattern = "ABC@XYZ";
nd = N Items( colsd );
selCols = {"LCOL", "WCOL"};


For( i = 1, i <= nd, i++,
If( Contains( colsd[i], pattern ),
Insert Into( selCols, colsd[i] )
)
);


dtB = dt << Subset(
	  All rows,
	  Columns(selCols)
);

colNameList= dtB << get column names( string );

Wait(1);

dt_sum_all = {}; 

For( i = 1, i <= N Items( colNameList ), i++,
	If( Contains( colNameList[i], pattern),
		dt_sum =  dtB << Summary(
	    Group( :LCOL, :WCOL ),
		Sum(colNameList[i]),
        Freq( "None" ),
	    Weight( "None" ),
		) << show window(0);	
		);
		Insert Into(dt_sum_all, dt_sum);
		//Data Table (dt_sum_all) << Update (
			///With(Data Table(dt_sum)),
			//Match Columns(:LCOL = :LCOL, :WCOL=:WCOL)
		);
		
	////column(dt_sum, "SUM("||colNameList[i] || ")"  ) << set name(colNameList[i]);	
	///);

The above code is not working and I am getting multiple tables for each column, instead of one merged/updated one. So not getting one table. Can anyone please help. Thanks.

Re: Concatenating/Merging/Updating Multiple Data Tables from a loop into one

txnelson — Sat, 05 Sep 2020 02:07:44 GMT

If I am reading your code correctly, what you want to end up with is a data table of summary data for a set of columns that match a pattern in the original data table.

I believe you were working a lot harder than you had to. Below is an example script, using most of your code, but working on the Semiconductor Capability sample data table, where the names of the lot and wafer columns are lot_id and wafer, rather than your Lcol and Wcol. I have eliminated the dtB data table, because unless you need it for something later in the script, it certainly does not need to be created just to then go and create summary data for it. The summary data can come directly from the original data table. Also, there is no need to summarize each column one at a time. All columns can easily be summarized at once. Thus, no Updatie need to be run. Finally, there is an option in the Summary Platform, that will allow for the column names to be given the same name as the input column name, so there is no need to go through the renaming of the columns in the summary table.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/semiconductor capability.jmp" );

Wait( 0 ); 

colsd = dt << get column names( string );
pattern = "PNP";
nd = N Items( colsd );
selCols = {"lot_id", "wafer"};


For( i = 1, i <= nd, i++,
	If( Contains( colsd[i], pattern ),
		Insert Into( selCols, colsd[i] )
	)
);

dt_sum = dt << Summary(
	Group( :lot_id, :wafer ),
	Sum( colNameList[Index( 3, N Items( colNameList ) )] ),
	Freq( "None" ),
	Weight( "None" ),
	link to original data table( 0 ),
	statistics column name format( "column" )
);

Re: Concatenating/Merging/Updating Multiple Data Tables from a loop into one

tnodd — Sat, 05 Sep 2020 04:35:22 GMT