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

VBIR

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

txnelson

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} );