How can I write a script to automatically group columns in new table?

Question

Hello,

I am currently writing a script to essentially join three "reference" tables together using the Join, Transpose, and Update functions. My script allows the final output table to be updated each time I add rows to the first "reference" table.

I was wondering if it would be possible to edit my script to automatically group columns (i.e. the "Group Columns" function) in the final output table based on the value in a column of the first "reference" table? This column is not brought along in any of the table functions as it would cause issues with transposition. It is important that these groupings remain for each update of the final table.

I appreciate any feedback you may have.

-Brian D.

txnelson · Answer

Take a look in the Scripting index for the uses of Group Columns. Here is one way of doing what you stated Names Default To Here( 1 ); dt = Open( "$SAMPLE_DATA/big class.jmp" ); dt2 = New Table( "groupings", New Column( "ID", character, set values( {"name", "age", "sex", "height", "weight"} ) ), New Column( "Type", character, set values( {"A", "B", "C", "B", "A"} ) ) ); // Split data into columns based on Type dtSplit = dt2 << Split( Split By( :Type ), Split( :ID ), Sort by Column Property ); // Process through the Split data table, creating the groups in the first data table For( Group = 1, Group <= N Cols( dtSplit ), Group++, theGroup = Column( dtSplit, Group ) << get values; For(i=nitems(theGroup), i>=1,i--,if(theGroup[i] == "", remove from(thegroup,i,1))); grouping = dt << group columns( theGroup );

dt << Rename Column Group( grouping, (column(dtSplit, group)< );

bhdarst · Answer

Not exactly, although a similar If statement might be useful. The reference table I mentioned (Table 1) has two relevant columns, "ID" and "Type". The final output table (Table 2) has the "ID" column transposed so that the values under that column in Table 1 are now column names in Table 2. What I'd like to do is group these columns in Table 2 based on their corresponding values under the "Type" column in Table 1.

txnelson · Answer

Is this the kind of thing you are looking for?

Names Default To Here( 1 );
dt1 = New Table( "table1", add rows(1), New Column( "test", set values( [1] ) ) );
dt2 = New Table( "table2", add rows(2),
	new column( "x1", character, set values( {"a", "b"} ) ),
	New Column( "x2", character, set values( {"c", "d"} ) )
);

If( dt1:test[1] == 1,
	dt2 << combine columns( delimiter( "," ), 
	columns( dt2:x1, dt2:x2 ), Column Name( "Combined" ) )
);