Inconsistencies in value ordering of numbers stored as character strings

[#1]

Hi,

I've recently found something curious in JMP Pro 14.2 : The default value of the value ordering property of a character column is not necessarily consistent with the sort order of the same column. An example : The French chart of accounts uses an articulated code as the ID of defined accounts. In such a system, 101 is a subdivision of 10, and so on. Thus, the ID must be stored as a character column, in order to have a sort order like 10, 101, 102, 11, 111, 12... Then all sorting operations or Col rank functions work fine but: the default value ordering in several plot is incorrect. Even when adding a Value Ordering property on the column, the defaut order defined by JMP is 10, 11, 12, 101... As an ordinary chart of accounts might have several hundred IDs, manual reordering is not an option.

I have found in another discussion (Value-Ordering-in-JMP-Script-by-a-Variable) a script that I was able to adapt, like:

• comptes = Column("Compte") << Get as Matrix;
• rangs = ranking(comptes);
• Column("Compte") << Set Property("Value Ordering", Eval(comptes[Rank Index(rangs)]));

But writing a script for something that should be automatic is rather unpleasant. Did I miss something and is there another solution ?

Thanks in advance

Yves Roy, Université de Poitiers

[#2]

I am not seeing the behavior you describe. Could you post an example? You said there are several plots which are incorrect. Which ones? Are they always the same types of plots? Could the issue be related to the modeling type of the ID column? Sometimes people confuse the Nominal modeling type with data being character. Although, in all of my cases it did not matter if the ID was character or numeric: the value ordering property was always obeyed. Because I cannot replicate what you will describe, I will need an example of how it is not working to try and determine why.

[#3 · Accepted solution]

Here is the example :

But I think I've got the answer a few minutes ago, watching John Sall's demonstration in Copenhagen JMP Europe Discovery Summit : This behaviour is new in JMP 14, named by John as "ghost ordering" or intelligent ordering of categorical data. 

The solution to my problem is then derived from what John Sall showed with value labels in the same video: Simply sort the table by account (no ghost ordering here), copy the entire colum and paste it in the Value ordering property box. One just have to first remove all values added by JMP before pasting and everything is OK.

Thanks a lot for your answer and I think the problem is solved now.