634606_en-US

liliconcom

Sort Column by column properties

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!

Automation and ScriptingRe: Sort Column by column properties

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++,<BR />    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

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

Thanks, Jim! 

Re: Sort Column by column properties

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) Any idea why Move Selected Columns works with a list was changed in JMP17? 

Re: Sort Column by column properties

Thanks, Jarmo!

Re: Sort Column by column properties

@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

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