topic Re: Alphanumeric sorting rules in JMP17 in Discussions

Alphanumeric sorting rules in JMP17

EdHutchins99 — Thu, 08 Jun 2023 16:44:38 GMT

I am using the Sort feature in JMP17.0.0 but am getting unexpected results. The column I wish to sort by contains a list of serial numbers where each is a mix of letter and numeric characters (examples: G95VXWW8CID5, G921J8D2CIE3, G943H085CIB2). The Sort-> Ascending action puts G95... ahead of G92... which is ahead of G94... This is unexpected because while the first two characters in all three serial numbers are the same, G9, in the third position, 2 should be before 4 which should be before 5. What are the rules by which JMP sorts alphanumeric values, and can those rules be edited?

Re: Alphanumeric sorting rules in JMP17

txnelson — Wed, 04 Jan 2023 12:32:41 GMT

Is there a Value Order column property set for the column you are sorting on? Are there unprintable characters(including a blank) in the values?

If you could provide a sample data table it would really help.

Re: Alphanumeric sorting rules in JMP17

Victor_G — Wed, 04 Jan 2023 12:50:21 GMT

Hi @EdHutchins99,

Welcome in the Community !

I have tried to reproduce your problem with the three IDs you mentionned.

I think the behaviour of JMP is normal and expected, as it will try to sort by using first alphanumeric characters encountered.

Since the first character is G for all (no sorting possible based on alphabet), JMP will do the sorting on the numbers just after. So when trying to sort, since you don't have the same number of numeric characters and 95 < 921 < 943, JMP will display this G95..., G92... and G94... order.

If you create a new column by recoding the ID column to only keep the first three alphanumeric character and then do the sorting on this new column, you should get the results you expect.

This is the formula script you can use in your new column if your original column is named ID (else you'll have to change ID by the name of the column you have in this formula) :

Recode( :ID, {Word( [1 3], _rcNow, "", Unmatched( _rcNow ) )} )

Hope this will help you,

Re: Alphanumeric sorting rules in JMP17

jthi — Wed, 04 Jan 2023 12:49:06 GMT

I'm not sure if you can affect how the sorting is done without using Column Properties. You could create "support" column which you can then sort by to get the order you want or you could maybe use Query Builder

or use Value Order Column Property

Some differences here when using functions for sorting:

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(3),
	New Column("Column 1",
		Character,
		"Nominal",
		Set Values({"G95VXWW8CID5", "G921J8D2CIE3", "G943H085CIB2"})
	)
);

a = dt[0, 1];
show(a);
b = Associative Array(Column(dt, 1));
Show(Sort List(a));
Show(b << get keys);
Show(Ranking(a));

dt << Sort(By(:Column 1), Replace Table, Order(Ascending));
Show(dt[0, 1]);
Write();

a = {"G95VXWW8CID5", "G921J8D2CIE3", "G943H085CIB2"};
Sort List(a) = {"G921J8D2CIE3", "G943H085CIB2", "G95VXWW8CID5"};
b << get keys = {"G921J8D2CIE3", "G943H085CIB2", "G95VXWW8CID5"};
Ranking(a) = [3, 1, 2];
dt[0,1] = {"G95VXWW8CID5", "G921J8D2CIE3", "G943H085CIB2"};

JMP table sort seems to be using Natural Sort Order (wikipedia) like @Victor_G mentioned and not alphabetical sorting

Re: Alphanumeric sorting rules in JMP17

Jed_Campbell — Wed, 04 Jan 2023 15:08:44 GMT

Also, if you add a Value Order property to the column, JMP should immediately recognize that your data are alphanumeric and ask if you'd like to configure it with a simple sort. If you answer yes, then hit ok in the following dialog (i.e., don't adjust anything, just accept), then the sorting will work as you expect.

Re: Alphanumeric sorting rules in JMP17

EdHutchins99 — Wed, 04 Jan 2023 15:33:46 GMT

Thanks, everyone! Setting the Value Order property, then sorting, puts the values in the order I expected.

Re: Alphanumeric sorting rules in JMP17

andersonmj2 — Wed, 04 Jan 2023 15:36:37 GMT

Agree with @Victor_G @jthi about the sorting rules reflecting Natural Sort Order ... you can see that Excel and JMP sort these differently. But I feel like we have spent so long trying to deal with Excel not understanding that in the below example 11 is larger than 2 that we forget that it is not normal. In your case, you have 943, 921 and 95. The potential problem is if your coding system would want 95 to be highest? Then I agree with @Victor_G about utilizing helper columns.

Alphabetical sorting:

Natural sorting:

Re: Alphanumeric sorting rules in JMP17

pcarroll1 — Wed, 15 Mar 2023 21:23:37 GMT

I have to respectfully disagree with the JMP choice of sorting. I know of no other software that does it this way and it is not at all obvious.

If the Data Type is specified as Character then it should be treated as such and ordered by accepted rules. Maybe JMP needs a new category Char/Num.

My set of data below is sorted by JMP as

0CNFB
0ERRB
01JAB
013BB
2X0UB
11DBB
13ZYB
17TSB
156QB
1474B

I understand the rules as explained here but who would think this is how these should be ordered? This is not the way to order a character field.

Re: Alphanumeric sorting rules in JMP17

txnelson — Thu, 16 Mar 2023 04:33:59 GMT

Your preferences are set to Numerical Ordering.

Go to

File=>Preferences=>Tables

and unselect the checkbox for Numerical Ordering