Using JSL script to sort a column in a data table using List Check or Value Order property

[#1]

Hi all,

I'd like to use the List Check feature or the Value Order column property to custom sort a column in a datatable. This data table is created from a subset of another data table, but I am unable to get the property to custom sort the values in the data table. The relevant commands I'm using is as listed below:

dt_summary_table = (Data Table( "Name of table" )) << Make Into Data Table; // This works fine and created a datatable. 

//Using List Check property
dt_summary_table << Column( "Column_name_eg_Names" ) << Set Property(
    "List Check",
    {"OrderList_A", "OrderList_B", "OrderList_C"}
);

// Or using Value Order property 
dt_summary_table << Column( "Column_name_eg_Names" ) << Set Property(
    "Value Order",
    {"OrderList_A", "OrderList_B", "OrderList_C"}
); 

//or using List Check property
dt_summary_table << Column( "Column_name_eg_Names" ) << List Check( {"OrderList_A", "OrderList_B", "OrderList_C"} );

// Or using Value Order property 
dt_summary_table << Column( "Column_name_eg_Names" ) << Value Order( {"OrderList_A", "OrderList_B", "OrderList_C"} );

Could someone please advise what might be going bad or best way to sort the column values in custom sort order while displaying graphs/ plots etc. 

I'm using JMP15 on Windows10.

[#2 · Accepted solution]

In JMP 15 the structure of the column property Value Order changed.  Here is an example of the new structure

names default to here(1);
dt=open("$SAMPLE_DATA\big class.jmp");

dt:sex<<set property("value order",{Custom Order({"M", "F"})});

dt << sort( by(:sex), replace table(1));

I suggest that you manually set the Value Order you want, and then use << get property("value order") to see how you need to structure your statement for your data

[#3]

Thanks Jim/@txnelson very much,

This works now. 

Just wondering if this new structure is documented somewhere ? I was following the documentation at https://www.jmp.com/support/help/en/15.0/#page/jmp/column-properties-3.shtml# which doens't seem to reflect the custom order syntax. 

[#4]

I agree with you that the documentation is in error.  

[#5]

Hello Jim,

When attempting to Set Property for Custom Order with a given List, the List is not interpolated/evaluated.

May I know how we can make it work with a given variable List ?

dt = Current Data Table();

order_list = { "Tony", "Robert", "Patrick", "Henry" };

dt:Names << Set Property(
	"Value Order",
	{ Custom Order( order_list ), Common Order( 0 ) }
);

In example above:

Running Custom Order( order_list ) sets the value order with a literal value named "order_list"

Running Custom Order( Eval(order_list) ) sets the value order with a literal value named "Eval"

Running Custom Order( EvalList(order_list) ) sets the value order with a literal value named "Eval List"

Literally listing the values in Custom Order without calling variable, only then it works.

[#6]

This should do the trick for you

dt = Current Data Table();

order_list = {"Tony", "Robert", "Patrick", "Henry"};

Eval(
	Eval Expr(
		dt:Names << Set Property(
			"Value Order",
			{Custom Order( Expr( order_list ) ), Common Order( 0 )}
		)
	)
);