Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
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
Highlighted
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
Highlighted
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.
Highlighted
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
Article Labels

    There are no labels assigned to this post.