Setting Value Order or Value Label by Another Column

[#1]

I have an interesting value ordering/labeling problem. See attached files. The data come from a gas chromatograph. The order that matters is not the alpha-sort of the name of the compound but the retention time (R.T.). There are 31 compounds = manual value ordering of the Compound column or manual value labeling of either the R.T. or MW Order columns will be painful and error-prone. Anyone working with chromatographic or spectral data probably has the same issue...

Is there a dialog-driven way to set the value labels or order by referring another column as reference?

Alternatively, does anyone have a script that can do this?

I've also posted an anonymized version of the data table.

Thanks in advance!

[#2 · Accepted solution]

This method assumes you would have to manually add the "MW Order" column, so it just looks at the "R.T" column.  No sorting of the table is needed.

Names Default To Here( 1 );
dt << New Column( "Order", numeric, Formula( Col Rank( :R.T. ) ) );
order_list = {};
Wait( 0 );
For Each( {{k, v}, i}, Associative Array( :Order, :Component ), Insert Into( order_list, v ) );
Eval( Eval Expr( :Component << Set Property( "Value Order", {Custom Order( Expr( order_list ) )}, Common Order( 0 ) ) ) );
dt << Delete Columns( "Order" );

[#3]

Thanks! It worked as intended*
* need to add as line #2: dt = Current Data Table ();

[#4]

wow, a very elegant way to map the order from one column to another column!

order_list={};
For Each( {{k, v}, i}, Associative Array( :Order, :Component ), Insert Into( order_list, v ) );

- Associative Array with column references:
  Associative Array( :Order, :Component ) !!! (--> find the documentation)
  automatically: duplicates get skipped, entries ... ordered by keys

- For Each( {{k, v}, i} ...  -> {v}

as an alternative:

Associative Array( :Order, :Component ) << get values

[#5]

As much as I work with associative arrays, I still forget about the <<Get Values message sometimes!

[#6]

application case:
sort states (with name = Abbreviation) by postal code:
Graph makeover: Measles heat map 

[#7]

You can do this also manually

Create summary table (use the statistics as necessary)

Sort your data

Set value order to the column you wish to order in the summary table

Copy Column properties from the summary table and paste to your original table

 

(I use modified scripted version of this process)