912484_en-US

liliconcom

Setting Value Order or Value Label by Another Column

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!

Data Blending and CleanupRe: Setting Value Order or Value Label by Another Column

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" );

 

Re: Setting Value Order or Value Label by Another Column

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

Re: Setting Value Order or Value Label by Another Column

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

 

Re: Setting Value Order or Value Label by Another Column

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)

Re: Setting Value Order or Value Label by Another Column

As much as I work with associative arrays, I still forget about the <

Re: Setting Value Order or Value Label by Another Column

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

Re: Setting Value Order or Value Label by Another Column

alternative solution by @ms 
how-to-sort-x-axis-by-another-column-value
Summarize + Rank Index