topic Re: Sort Column by column properties in Discussions

Sort Column by column properties

Jackie_ — Tue, 23 May 2023 20:54:33 GMT

Hi JMP Community,

Is there a way to sort the columns by the column property?

I have a data table where the column contains the property "Test Number" (see below).

I want to sort all the columns in the column group "Tests" by the column property "Test Number"

I have attached the data table below. Any suggestions?

Thanks, Jackie!

Re: Sort Column by column properties

StarfruitBob — Tue, 23 May 2023 21:59:25 GMT

Hello @Jackie_ ,

I expect something like this to work for what you want. The syntax is probably incorrect, but the idea is on the right path. I believe using the associative arrays is the easiest path, since it automatically sorts entries in ascending / alphabetical order inherently.

Note that I've left group_tests_cols empty. I see you've grouped them in "Tests", so you probably have a list, or a method for retrieving all of the column names to create a list.

Names default to here(1);

dt = current data table();

group_tests_cols = {  };

// automatically sorts key data in ascending / alphabetical order
aa_tests_cols = Associative Array();

// Puts all of the test numbers as the keys in the AA
for( a = 1, a <= N Items( group_tests_cols ), a++,	
	test_number = column( dt, group_tests_cols[a] ) << Get Property( "Formula" );	
	eval( eval expr( aa_tests_cols[ expr( test_number ) ] ) ) = group_tests_cols[a];	
);

aa_keys = aa_tests_cols << Get keys;

// Moves columns in the order of the keys
for( b = 1, b <= N Items( aa_keys ), b++,
    col_name = aa_tests_cols[ aa_keys[b] ]; // uses index of the key to retrieve the actual value of the key, to get the col name
	eval( eval expr( expr( column( dt, col_name ) ) << Move Selected Columns( expr( { as column( col_name ) } ), after( :name ) ) ) );
);

Re: Sort Column by column properties

txnelson — Tue, 23 May 2023 22:29:42 GMT

Here is a working script that finds the test number values and then orders the columns based upon them.

Names Default To Here( 1 );
dt = Current Data Table();

//Get the test numbers and the column names associated with them
valList = {};
testList = {};
For Each( {col}, dt << get column group( "Tests" ),
	Show( col );
	Insert Into( valList, Num( col << get property( "Test Number" ) ) );
	Insert Into( testList, Char( col << get name ) );
);

// Sort the test numbers
sortedList = Sort List( valList );

// Loop through the test orders and reorder the columns
For Each( {test, index}, sortedList,
	theCol = Contains( valList, sortedList[N Items( sortedList ) + 1 - index] );
	dt << go to( testList[theCol] );
	dt << Move Selected Columns( To first );
);

Others may have a better way to accomplish this task

Re: Sort Column by column properties

Jackie_ — Tue, 23 May 2023 22:35:32 GMT

Thanks, Jim!

Re: Sort Column by column properties

jthi — Wed, 24 May 2023 10:20:03 GMT

I think this would work in earlier versions than JMP17

Names Default To Here( 1 );
dt = Current Data Table();

aa = Associative Array();

For Each({col}, dt << get column group("Tests"),
	aa[col << get name] = Num(col << get property("Test Number"));
);

Eval(EvalExpr(
	dt << Move Selected Columns(Expr((aa << get keys)[Rank(aa << get values)]), To last)
));

In JMP17 (at least JMP17.0) the behaviour of Move Selected Columns was changed (by accident)

Re: Sort Column by column properties

Jackie_ — Wed, 24 May 2023 17:24:11 GMT

Thanks, Jarmo!

Re: Sort Column by column properties

Jackie_ — Thu, 27 Jul 2023 16:36:47 GMT

@jthi I on JMP 17 and the move select doesn't work.

Muliple Move Selected Columns commands wouldn't be robust perAny idea why Move Selected Columns works with a list was changed in JMP17?

Any workaround that you can suggest for this:

Names Default To Here( 1 );
dt = Current Data Table();

aa = Associative Array();

For Each({col}, dt << get column group("Tests"),
	aa[col << get name] = Num(col << get property("Test Number"));
);

Eval(EvalExpr(
	dt << Move Selected Columns(Expr((aa << get keys)[Rank(aa << get values)]), To last)
));

Re: Sort Column by column properties

jthi — Thu, 27 Jul 2023 17:55:59 GMT

Performing multiple move selected columns within a loop should work. You just have to first create the ordering list for the columns and then loop accordingly