topic Re: Write a function to join data tables in Discussions

Write a function to join data tables

DivisiveEagle45 — Tue, 16 Apr 2024 10:58:51 GMT

I was trying to write a function to join dt2 to dt1, however, I cannot get it to work at all... The error I got is "Name Unresolved: TrimTable at row 1 in access or evaluation of 'TrimTable' , TrimTable( dt1[1] ) /*###*/"

What is wrong?

Much appreciated for the help!

join_data_table = Function({dt1, dt2},
	{Default Local},
	dt_1 = TrimTable(dt1[1]);
	dt_2 = TrimTable(dt2[1]);
	dt_1 << Join(
		With(dt_2),
		Merge Same Name Columns,
		By Matching Columns(:"Seq No"n = :Seq No),
		Drop multiples(1, 0),
		Include Nonmatches(1, 0),
		Preserve main table order(1)
	);

	Return(dt1);

);

Re: Write a function to join data tables

jthi — Tue, 16 Apr 2024 11:02:28 GMT

What is dt1 supposed to contains? What does TrimTable do? Could be that TrimTable() function is missing.

Maybe you want something like this?

Names Default To Here(1);

join_data_table = Function({dt1, dt2}, {Default Local},
	dt = dt1 << Join(
		With(dt2),
		Merge Same Name Columns,
		By Matching Columns(:name = :name), // Seq No changed to Name
		Drop multiples(1, 0),
		Include Nonmatches(1, 0),
		Preserve main table order(1)
	);
	
	return(dt);
);

dt_bc = Open("$SAMPLE_DATA/Big Class.jmp");
dt_bcf = Open("$SAMPLE_DATA/Big Class Families.jmp");

join_data_table(dt_bc, dt_bcf);

Re: Write a function to join data tables

DivisiveEagle45 — Tue, 16 Apr 2024 16:27:31 GMT

Thank you so much. It is very interesting that the script you gave to me works on the examples but does not work on my data tables. The dt1 is suppose to be a target table and dt2 is a processed data table, I want to join dt2 to dt1 based on column name.

With your script, this is the error: Join with Data Table unknown at row 1 in access or evaluation of 'With' , With( dt2 ) /*###*/

Both dt1 and dt2 were open.

Let me know if any more info is needed.

Re: Write a function to join data tables

DivisiveEagle45 — Tue, 16 Apr 2024 16:36:59 GMT

final_data_table = DataTable("Target Table");
dt = {DataTable("Annotated data")};

I got it to work! the problem was after preprocessing, the dt actually became a datatable stored in a list. change dt to dt[1] resolved the issue.