How to concat two unequal tables horizontally (not a simple ‘join’ case)

[#1]

Table 1 looks like

patient	assay result 1	assay result 2	assay result 3
1001	1	a	+
1001	2	b	+
1002	1	c	-
1003	2	d	-

Table 2 looks like

patient	adverse event
1001	aaa
1002	aab
1002	aac
1002	aad
1003	bbb
1003	bbc

The result I want to get looks like:(patient column is a 'shared column' for these two tables)

patient	assay result 1	assay result 2	assay result 3	adverse event
1001	1	a	+	aaa
1001	2	b	+
1002	1	c	-	aab
				aac
				aad
1003	2	d	-	bbb
				bbc

Could someone provide me a solution(as simple as possible)?

Thanks

[#2 · Accepted solution]

The task can be accomplished by adding a counter column to both tables, counting each of the number of repeated values of patient there are

Then the join can be done, matching on patient and counter

Finally, sorting the data back to patient sort value and doing a little cleanup the task is realized

Here is the JSL to get the results, but each task within the JSL can easily be done interactively

Names Default To Here( 1 );

// Create example data tables
dt1 = New Table( "Table1",
	Add Rows( 4 ),
	New Column( "patient", character, Set Values( {"1001", "1001", "1002", "1003"} ) ),
	New Column( "assay result 1", Character, "Nominal", Set Selected, Set Values( {"1", "2", "1", "2"} ) ),
	New Column( "assay result 2", Character( 16 ), "Nominal", Set Values( {"a", "b", "c", "d"} ) ),
	New Column( "assay result 3", Character( 16 ), "Nominal", Set Values( {"+", "+", "-", "-"} ) )
);

dt2 = New Table( "Table2",
	Add Rows( 6 ),
	New Column( "patient", Character, Set Values( {"1001", "1002", "1002", "1002", "1003", "1003"} ) ),
	New Column( "adverse event", Character( 16 ), "Nominal", Set Values( {"aaa", "aab", "aac", "aad", "bbb", "bbc"} ) )
);

dt1 << New Column( "counter",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula(
		If( Row() == 1, c = 1 );
		If( Lag( :patient ) != :patient,
			c = 1,
			c
			++);
		c;
	)
);

// Add a counter column to both data tables
dt2 << New Column( "counter",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula(
		If( Row() == 1, c = 1 );
		If( Lag( :patient ) != :patient,
			c = 1,
			c
			++);
		c;
	)
);

// Join the tables matching upon patient and counter
dt3 = dt1 << Join(
	With( dt2 ),
	Merge Same Name Columns,
	By Matching Columns( :patient = :patient, :counter = :counter ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 1 ),
	Preserve main table order( 1 )
);

// Sort the data into patient order
dt3 << sort( by( :patient, :counter ), order( ascending, ascending ), replace table( 1 ) );

// If the data came from only the "With" data table, delete the patient value
dt3:patient[dt3 << get rows where( :match flag == 2 )] = "";

// Clean up the table
dt3 << delete columns( {:Match Flag, :counter} );

[#3]

Hi @VBIR ,

As @txnelson mentioned, to get the table to look exactly as you shown, you would have to do some scripting or multiple interactive steps. However, JMP tables really are not designed to display data in that way. They are data tables, not spreadsheets, so the simple join would look like this from a JMP data table point of view

This is because JMP tables are used for analytical and graphing purposes, where as spreadsheets are for making numerical summaries that are easier to read. In the clinical trial world, data is typically stored in the fashion shown in the image, but might be displayed more like what you have shown.

So that leads to the next question, what is your goal with displaying the joined data in the fashion that you have given us? Is it for reporting purposes or are you planning to do some additional graphing or analysis with the result?

[#4]

Oh, I forgot to mention, when measuring an assay multiple times for a patient, each measurement really should have its own ID to make sure there is proper alignment of adverse events (which would have a matching ID) to each measurement without possible confusion (like in my example were adverse event aaa was repeated for patient 1001, but should not have been, I assume.

Typically a date-time stamp (or study day) for adverse event and date-time stamp (or study day) for each measurement for each assay. That way if the adverse events are supposed to be for only one of the reported measurements for each assay, it will be correctly displayed. Same for multiple adverse events for the same day that the assay result was recorded. For example:

Table 1

Patient	Study Day	Assay 1	Assay 2	Assay 3
1001	1	1	a	+
1001	2	2	b	+

Table 2

Patient	Study Day	Adverse Event
1001	1	aaa
1001	2

Table 3 (simple join with Patient and Study Day as matching columns and using included non-matching from both tables)

Patient	Study Day	Assay 1	Assay 2	Assay 3	Adverse Event
1001	1	1	a	+	aaa
1001	2	2	b	+

This prevents ambiguous accidental assignments that might be made and allows for use in JMP for further analysis and graphing. Also, row 2 in table 2 is not really needed when joining because it would be assumed that there are no events on Study Day 2. 

[#5]

@Chris_Kirchberg thanks for your patience and very detailed answers!

I should have noted that this is an example I made out of thin air.

I just want to create a table looks like this example for other programming teammates so that they can compare LB and AE by easily scrolling the screen.

[#6]

Thank you @txnelson , it works!