cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
bhdarst
Level I

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

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.

3 REPLIES 3
txnelson
Super User

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

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" ) )
);
Jim
bhdarst
Level I

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

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
Super User

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

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)<<get name)); );
Jim