topic Re: How to use a column to "Value Order" another column in Discussions

How to use a column to "Value Order" another column

SDF1 — Sat, 10 Jun 2023 23:24:43 GMT

Dear JMP Community,

Is it possible to use the numerical values (integers) of one column as a way to "Value Order" another column?

I have a text column that I'd like to have ordered according to another column that contains integers. As an example, I have a column called :Text and one called :Level.

By default, JMP orders the "Value Order" column property as such:

I'd prefer to have it ordered like this:

Which, in my simple example is the same order as the column :Level. I can do this manually of course, but in my real data table, I have a text column with several dozens of entries that I'd prefer to not do manually, but instead use the column :Level to set the "Value Order" property of the :Text column.

Is there a way to script this or do it through a GUI within JMP?

Thanks!,

Re: How to use a column to "Value Order" another column

jthi — Tue, 05 Jan 2021 16:42:56 GMT

I don't know if it is possible with GUI but you can script it with couple of steps:

First get correctly ordered Text column in list
Add column property to Text column with the previous list

Below is one possible solution that should work at least with the current example (open Log window to see "debug" prints):

Names Default To Here(1);

dt = New Table("table",
	Add Rows(10),
	Compress File When Saved(1),
	New Column("Text",
		Character,
		"Nominal",
		Set Values({"A", "B", "C", "D", "E", "F", "AA", "BB", "CC", "DD"})
	),
	New Column("Level",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([1, 2, 3, 4, 5, 6, 7, 8, 9, 10])
	)
);
//get values from Level and Text into Associative Array
aA_level_text = AssociativeArray(Column(dt, "Level") << Get Values,Column(dt, "Text") << Get Values);
show(aA_level_text); //debug
ordered_level = aA_level_text << Get Values; //Get values (will be ordered by Level)
Show(ordered_level); //debug
Show(Column(dt, "Text") << Get Property("Value Ordering")); //debug

//Add Value Ordering Column Property to Text column
Eval(Eval Expr(
	Column(dt, "Text") << Set Property("Value Ordering", Expr(ordered_level));	
));
Show(Column(dt, "Text") << Get Property("Value Ordering")); //debug
dt << Clear Column Selection;

Re: How to use a column to "Value Order" another column

ron_horne — Tue, 05 Jan 2021 16:59:29 GMT

Hi @SDF1

If all you need is to use it in Graph builder you can order any axis by the other as in the picture. you can even change the statistic for the ranking. this will not help you if you want the ordering for testing or usage in other analysis or graphing platforms.

what i do in most cases is produce a table summary by the other variable and sort it. from there i copy paste the categories directly to the value ordering. It is a hack not everyone knows but it works.

Ron

Re: How to use a column to "Value Order" another column

txnelson — Tue, 05 Jan 2021 18:51:10 GMT

Both @jthi and @ron_horne provide nice solutions. But if you just want an interactive way to do this all you have to do is

order the data table by the column you want the data to be ordered by
Go into the Value Ordering Column Property for the column you want to use for the ordering and select "Row Order Levels

Now when you use the column you specified Row Order Levels as an ordering column, it will use the order in the data table for that column as the order to display the results as.

Re: How to use a column to "Value Order" another column

SDF1 — Tue, 05 Jan 2021 19:13:56 GMT

Hi @ron_horne ,

Thanks for your feedback. I am familiar with that method for making a Pareto-like plot in Graph Builder; have used it many times, and it's quite helpful. Unfortunately, I am doing a lot of other evaluations with the data table than just the data visualization, so it would help to have a solution that is portable and scalable to tables with thousands of rows and several dozen text options. I haven't tried the other two methods yet, but will given them a shot.

Thanks!,

Re: How to use a column to "Value Order" another column

SDF1 — Tue, 05 Jan 2021 19:24:29 GMT

Hi @txnelson ,

Thanks for your feedback. Unfortunately this solution doesn't work with the :Text column. If I sort the table according to the :Level column, and then apply the "Row Order Levels" as you suggest for the value order of the :Text column, it still keeps the alpha-ordering of the :Text column, meaning that AA still comes after A and so on.

The other "problem" is that it messes up the rows from the original data table, which are currently sorted by date. The :Text and :Level columns can be scattered throughout the dates and sometimes keeping the table date-sorted helps out. It's not really a big problem, but I know that other users who would then be accessing the data table won't like it.

Thanks!,

Re: How to use a column to "Value Order" another column

SDF1 — Tue, 05 Jan 2021 19:54:52 GMT

Hi @jthi ,

Thanks for your feedback. Yes, this JSL code is exactly what I need as it can handle any odd arrangement of the :Text and :Level columns and then assign the value ordering appropriately.

I have not explored or worked much with the associative array command, but clearly I need to look more into that! Very nice, just 3 lines of code.

Thanks!,

Re: How to use a column to "Value Order" another column

txnelson — Tue, 05 Jan 2021 19:55:15 GMT

I made an error in my description of what to do. Here is an example using the Big Class data table

I sorted the data table by Heigh t
I then went into the column property for the Name column and selected Row Order Levels, AND I unselected Custom Order
I then opened Graph Builder and dragged the Name Column to the X Axis, and as can be seen, the order of the X axis is the Row Order of the Names

Re: How to use a column to "Value Order" another column

SDF1 — Tue, 05 Jan 2021 20:02:50 GMT

Hi, yes, I can see now why you need to uncheck the custom order check box. That makes sense. The nice thing about the way jthi does it is that no matter the sorting of the data table, it maintains the desired value ordering of the :Text column.

Re: How to use a column to "Value Order" another column

txnelson — Tue, 05 Jan 2021 20:25:26 GMT

I agree, @jthi solution is best. Some folks don't like JSL. Lots of folks are not aware of the Row Order Levels. Excel users (a generalization comment is coming) would like to have Row Order Levels as the default. Thus, that is my motivation to respond to your fine discussion question.

Re: How to use a column to "Value Order" another column

kachveder — Thu, 30 Nov 2023 15:44:48 GMT

Hi !

I am trying to order one column (batches) by values of a numeric date column but am getting an issue where when two different batches were manufactured on the same day.

For example, why would this not order correctly?

dt = New Table("table", Add Rows(10), Compress File When Saved(1), New Column("Text", Character, "Nominal", Set Values({"A", "B", "C", "D", "E", "F", "AA", "BB", "CC", "DD", "EE", "FF"}) ), New Column("Level", Numeric, "Continuous", Format("Best", 12), Set Values([1, 2, 2, 3, 3, 4, 5, 6, 7, 8, 9, 10]) ) );

The order of the text column would be the following:

text level

A 1

B 2

D 3

F 4

AA 5

BB 6

CC 7

DD 8

EE 9

FF 10

C 2

E 3

Instead of the desired:

text level

A 1

B 2

C 2

D 3

E 3

F 4

AA 5

BB 6

CC 7

DD 8

EE 9

FF 10

C 2

Re: How to use a column to "Value Order" another column

jthi — Fri, 01 Dec 2023 05:25:04 GMT

The solution here is using associative array which requires you to have unique keys. So if you have duplicated levels, some of the text you have will be "deleted" (and they will be ordered by default ordering I would guess).

Re: How to use a column to "Value Order" another column

kachveder — Fri, 01 Dec 2023 13:30:12 GMT

OK, I'll give it a try

Re: How to use a column to "Value Order" another column

jthi — Fri, 01 Dec 2023 13:39:37 GMT

Using Ranking() to "re-order" might work in your case

Names Default To Here(1);

dt = New Table("table",
	Add Rows(10),
	Compress File When Saved(1),
	New Column("Text", Character, "Nominal", Set Values({"FF", "B", "C", "D", "E", "F", "AA", "BB", "CC", "DD", "EE", "A"})),
	New Column("Level", Numeric, "Continuous", Format("Best", 12), Set Values([10, 2, 2, 3, 3, 4, 5, 6, 7, 8, 9, 1]))
);

m_text = Column(dt, "Text") << get values;
m_level = Column(dt, "Level") << get values;

orders = Ranking(m_level);
m_text[orders];

Show(Column(dt, "Text") << Get Property("Value Ordering")); //debug
Eval(Eval Expr(Column(dt, "Text") << Set Property("Value Ordering", Expr(m_text[orders]))));
Show(Column(dt, "Text") << Get Property("Value Ordering")); //debug

Re: How to use a column to "Value Order" another column

kachveder — Fri, 01 Dec 2023 14:25:13 GMT

It worked! Thank you so much jthi!

Re: How to use a column to "Value Order" another column

Ressel — Tue, 19 Aug 2025 20:31:45 GMT

Interesting, that the ordinal column "age" cannot be selected in the pop-up menu. Any idea why not?

Re: How to use a column to "Value Order" another column

jthi — Wed, 20 Aug 2025 04:29:13 GMT

You can in later versions of JMP as they offer option "Other" which lets you pick a column.

Re: How to use a column to "Value Order" another column

hogi — Tue, 23 Dec 2025 22:00:52 GMT

similar posts with elegant solutions:

via Associative Array

https://community.jmp.com/t5/Discussions/how-to-sort-x-axis-by-another-column-value/m-p/442639#M69092 :
Summarize + Rank Index