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

[#1]

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!,

DS

[#2 · Accepted solution]

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

1. First get correctly ordered Text column in list
2. 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;

[#3]

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!,

DS

[#4]

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

[#5]

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).

[#6]

OK, I'll give it a try

[#7]

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

[#8]

It worked! Thank you so much jthi!

[#9]

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

[#10]

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

1. order the data table by the column you want the data to be ordered by
2. 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.