topic Re: Automate concatenating data tables based on name in Discussions

Automate concatenating data tables based on name

MedianRooster10 — Thu, 02 Nov 2023 16:04:03 GMT

Hi,

I want to concatenate data tables based on their name.

For example I have 6 Data tables, and their names will be something like M1a M1b M2a M2b M3a M3b. So I want to join all M1 devices together etc. However, the M1 part will vary by name so I need the script to be able to get name and then compare part of the name.

Any help to get started is very appreciated

Re: Automate concatenating data tables based on name

jthi — Thu, 02 Nov 2023 17:32:18 GMT

Are the tables already open? If not, are they in same folder and are there other files in the folder?

<< Get Name can be used to get the name of data table and Get Data Table List() a list of open data tables. To compare data table names there are many options and which to use depends on your data table names (Regex is one option).

Re: Automate concatenating data tables based on name

txnelson — Thu, 02 Nov 2023 17:51:18 GMT

Here is a script that should give you a start at putting your final script together. It creates some sample tables to use for the example, and then finds them and matches on the first 2 characters in the name and finally concatenates them.

Names Default To Here( 1 );
// create sample data tables
For( i = 1, i <= 6, i++,
	New Table( "M" || Char( Mod( i, 3 ) + 1 ) || Char( i ), New Column( "D" ) );
	Data Table( "M" || Char( Mod( i, 3 ) + 1 ) || Char( i ) ) << add rows( 1 );
	Column( Data Table( "M" || Char( Mod( i, 3 ) + 1 ) || Char( i ) ), 1 )[1] = i;
);

// Find all data tables that match Mx
concatTableList = {};
shortNameList = {};
For( i = 1, i <= N Table(), i++,
	If( Starts With( Data Table( i ) << get name, "M" ),
		Insert Into( concatTableList, Data Table( i ) << get name );
		Insert Into( shortNameList, Substr( Data Table( i ) << get name, 1, 2 ) );
	)
);
// Find the unique key values
theKeys = Associative Array( shortNameList ) << get keys;

// Create the concatenated tables
For Each( {key}, theKeys,
	catList = {};
	For Each( {tableName}, concatTableList,
		If( Starts With( tableName, key ),
			Insert Into( catList, tableName )
		)
	);
	If( N Items( catList ) > 1,
		dt = New Table( key );
		For Each( {cat}, catList, dt << concatenate( Data Table( cat ), append to first table ) );
	);
);

Re: Automate concatenating data tables based on name

jthi — Thu, 02 Nov 2023 19:35:46 GMT

Also you might consider using Multiple File Import. Either combine all the tables into single table and then separate them into subsets using source column (create new column based on source column for the groups you want to have) or run MFI multiple times using wildcards * (match zero or more characters), ? (match exactly one character) and ; (separate different file name patterns) to get all single group with one run of MFI

Re: Automate concatenating data tables based on name

MedianRooster10 — Fri, 03 Nov 2023 09:03:07 GMT

I don't think this can work as they all come from the same excel workbook. I open this workbook and each sheet becomes a new data table. Is there a way for this to work?

Re: Automate concatenating data tables based on name

jthi — Fri, 03 Nov 2023 09:14:57 GMT

If it is just a single excel file you can use Excel Import Wizard.

Enable "Concatenate worksheets and try to match columns" and "Create column with worksheet name when concatenating". After you have data in JMP table, create grouping column and create subsets as needed.

Re: Automate concatenating data tables based on name

MedianRooster10 — Fri, 03 Nov 2023 09:20:06 GMT

Thank You