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
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=""></GET> 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=""></NEW>
This looks very promising!! Thank you! I'll let you know if it works.
Edit: Spent the morning going through your code. It's a great start for me to figure out how to build more custom tables. Thank you! I appreciate the time and effort. Is there a way to add a span box above the columns to an already existing table? For example, let's say I organized the table without the extra categorization. I don't want to go column by column to piece together the table. I just want to pop it up above. Hope that makes sense.
