How to create subcategories for rows and columns similar to tabulate

svarga

I'm currently working on creating a data table of results from a SQL query. The final data table has a set up that merges the shared column groups and merges the rows of shared values as well (see Table 1).

 

The data sometimes has numeric values, but most of the results are strings like "Negative" or "< 0.1". The way I summarize the data table with the character/nominal values is by transposing the results based on the type of test. I can group the columns, but when I send the data table to a new window as a report or to a journal page, it doesn't display the grouped columns. I can't use Tabulate because it only summarizes numeric values and it also won't allow you to keep the formatting when you make it into a data table. I also can't color the cells within a Tabulate table. I just want the ability to add subcategories and nested columns/rows like tabulate does to a regular data table. I'm wondering if there's any way to achieve what I'm looking for within JMP using JSL code, and if there isn't, can this be a feature request?

 

Table 1. Example of Final Table

 

ErraticAttack

JMP does not allow you to group rows.  Tabulate does not group rows, it simply leaves some rows blank (see below).

 

If you look at the tree structure for Tabulate then you'll notice that JMP uses special constructs such as MultiTblStrColBox and MultiTblNumColBox to create the tabulate's table.  These are not something that can be created in JSL by the user.  The next best thing is to utilize the <<Get As Report with some fancy group and name handling to create something similar with TableBox -- which we do have access to in JSL.  Here is a quick function "tabulatish" that I threw together that can take a table that has specified colors on it's cells with any column grouping you may desire and creates something like tabulate.  You do need to specifiy a table and special categorical column within:

 

 

Names Default To Here( 1 );
table = Open( "$SAMPLE_DATA/Blood Pressure.jmp" );
theGroup = table << Group Columns( "BP", :BP 8M :: :BP 8W );
theGroup = table << Group Columns( "ELSE", {:BP 12W, :BP 6W} );
For( i = 1, i <= N Cols( table ), i++,
	For Each Row( table,
		Column( table, i ) << Color Cells( Eval List( Repeat( {Random Uniform( 0.9, 1 )}, 3 ) ), Row() )
	);
);

tabulatish = Function( {table, category},
	{Default Local},
	column names = table << Get Column Names( "String" );
	For( i = 1, i <= N Items( column names ), i++,
		If( Uppercase( category ) == Uppercase( column names[i] ),
			category = column names[i];
			Break();
		)
	);
	Eval( Parse( Eval Insert( JSL Quote(
	table << Sort( By( :Name("^category^") ), Order( "Ascending" ), "Replace Table" );
	) ) ) );

	categories = Associative Array( Column( table, category ) ) << Get Keys;
	locs = Repeat( {""}, N Rows( table ) );
	For( i = 1, i <= N Items( categories ), i++,
		locs[(Loc( Column( table, category ) << Get Values, categories[i] ))[1]] = categories[i]
	);
	
	nt = New Table( "temp", <<New Column( category, "Character", <<Set Values( locs ) ), Private );
	olb = Outline Box( "",
		nt << Get As Report
	);
	Close( nt, No Save );
	
	group names = table << Get Column Groups Names();
	groups = {};
	For( i = 1, i <= N Items( group names ), i++,
		Insert Into( groups, table << Get Column Group( group names[i] ) )
	);
	For( i = 1, i <= N Items( groups ), i++,
		groups[i] = Char( groups[i] )
	);
	
	Remove From( column names, Contains( column names, category ) );

	While( 1,
		cols = {};
		Insert Into( cols, column names[1] );
		If( Contains( groups, column names[1] ),
			For( i = 1, i <= N Items( group names ), i++,
				names = table << Get Column Group( group names[i] );
				For( j = 1, j <= N Items( names ), j++,
					names[j] = Char( names[j] )
				);
				If( Contains( names, column names[1] ),
					cols = names;
					Break();
				)
			)
		);
		t = table << Subset(  All Rows, Columns( cols ), Not Linked, Private );
		report = t << Get As Report;
		If( N Items( cols ) == 1,
			olb[Table Box( 1 )] << Append( Try( report[String Col Box( 1 )], report[Number Col Box( 1 )] ) );
		,
			span box = Col Span Box( group names[i] );
			For( i = 1, i <= N Items( cols ), i++,
				span box << Append( Try( report[String Col Box( i )], report[Number Col Box( i )] ) )
			);
			olb[Table Box( 1 )] << Append( span box )
		);
		Close( t, No Save );
		column names = Associative Array( column names );
		column names << Remove( Associative Array( cols ) );
		column names = column names << Get Keys;
		If( N Items( column names ) == 0, Break() );
	);
	olb[Table Box( 1 )] << Set Scrollable( 30, N Cols( table ) + 25 ) << Set Shade Alternate Rows( 0 );
	olb
);

New Window( "Test",
	tabulatish( table, "dose" )
);

Close( table, No Save );