topic Re: Merge data tables in Discussions

Merge data tables

MBL — Sat, 10 Jun 2023 23:41:13 GMT

Hi all,

I need to add the data in the column "Result_test2" from the table "Untitled 23" to a new column in the table "Untitled 22". The data must be arranged as shown in the table "Untitled 27". For each batch in the first table ("Untitled 22") there are 10 data points per batch, but in the second table ("Untitled 23") there are only 3 data points per batch. I have tried various possibilities with the JOIN function, but it does not give me what I need.

Help is greatly appreciated

Best regards,

Re: Merge data tables

jthi — Fri, 03 Dec 2021 08:09:02 GMT

Add grouping row order column to both of tables:

Untitled 22:

Col Cumulative Sum(1, :Batch no. 1)

Untitled 23:

Col Cumulative Sum(1, :Batch no_2)

Join with something like this:

Remove extra columns:

Re: Merge data tables

txnelson — Fri, 03 Dec 2021 08:25:08 GMT

This is a fairly simple thing to do.

To do this interactively you would

Open both data tables

Go to table one and create a new column called "subcount" with the following formula

If(
			Row() == 1, x = 1,
			Lag( :Batch no. 1 ) == :Batch no. 1, x++,
			x = 1
		);
		x;

Go to the second table and create a new column also called "subcount" with a slightly different formula, because of the different column names in the second table
```
If(
			Row() == 1, x = 1,
			Lag( :Batch no_2 ) == :Batch no_2, x++,
			x = 1
		);
		x;
```
Click on the first data table to make it the active table
Go to Tables=>Update
1. Select the second table as the table to get the updates from
2. Select column Batch no. 1 from the first table and column Batch no_2 from the second column to match on
3. Select column subcount from the first table and column subcount from the second data table as the second pair of columns to match on
4. Click on OK
Click on the column header for column subcount and then right click and select delete column
Repeat the above step for the second data table

Below is a script that will perform the same actions as the interactive steps above

Names Default To Here( 1 );
dt1 = Data Table( "untitled 22" );
dt2 = Data Table( "untitled 23" );

dt1 << New Column( "subcount",
	formula(
		If(
			Row() == 1, x = 1,
			Lag( :Batch no. 1 ) == :Batch no. 1, x++,
			x = 1
		);
		x;
	)
);

dt2 << New Column( "subcount",
	formula(
		If(
			Row() == 1, x = 1,
			Lag( :Batch no_2 ) == :Batch no_2, x++,
			x = 1
		);
		x;
	)
);

dt1 << Update( With( dt2 ), Match Columns( :Batch no. 1 = :Batch no_2, :subcount = :subcount ) );

dt1 << delete column(subcount);
dt2 << delete column(subcount);

Re: Merge data tables

MBL — Fri, 03 Dec 2021 09:25:48 GMT

It works. Thank you very much. Incredible how something so complex for one, can be so simple for another.

Re: Merge data tables

MBL — Fri, 03 Dec 2021 09:27:13 GMT

I tested this solution also and it gives the needed result also - with the script just faster. Thank you to you.