Merge data tables

[#1]

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,

M

[#2]

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:

[#3 · Accepted solution]

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

[#4 · Accepted solution]

This is a fairly simple thing to do.  

To do this interactively you would

1. Open both data tables
2. 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;

3. 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;

4. Click on the first data table to make it the active table
5. 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
6. Click on the column header for column subcount and then right click and select delete column
7. 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);

[#5]

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