How to create subcategories for rows and columns similar to tabulateI'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
Mass CustomizationSharing and Communicating ResultsWindowsRe: How to create subcategories for rows and columns similar to tabulateThat's what I thought but figured I'd ask. Thanks again for the help!
Re: How to create subcategories for rows and columns similar to tabulateI don't think you'd be able to do that very easily. Due to the hierarchical nature of the display tree you can't simply insert a display box such that objects that weren't its children then become its children. You could insert a ColSpanBox, then using <<CLONEBOX></CLONEBOX> you could insert children into the ColSpanBox, but at that point you'd be better off just creating it from scratch
Re: How to create subcategories for rows and columns similar to tabulateThis 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.
Re: How to create subcategories for rows and columns similar to tabulateJMP 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>
