Subscribe Bookmark RSS Feed

JMP: Having difficulties sorting a list.

dlh

Community Trekker

Joined:

Oct 9, 2012

I have a table with columns named A, B, C, D for instance. These columns contain numbers and are set to Numeric, Continuous. I am trying to find the second highest value in these four columns using the Sort List function within a JMP Column formula. If I do something like Sort List( { 22, 44, -54, 33} )[3] it returns 33, which is correct. But if I replace this with the named columns such as Sort List( {:Name("A"), :Name("B"), :Name("C"), :Name("D")}[3] It gives me an error "Expression does not fit available column types 1 times At rows: {1} Operation: Bad Argument.

So if I do a combination of this like, Sort List( {:Name("A"), 44, -54, 33})[3] it returns 44 independent of what is in A. My theory is that it is ignoring the named field. To test this theory, I change the subscript from 3 to 4 and now it returns the same error as above.

I have tried wrapping the named column with a Num() function call. I have tried replacing the Sort List function call and the subscript with N Items function call it always says that this is a list of 4 items.

Any hints? Is there a better way to approach this?

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

If your data table has only one row, use the following:

Sort List(Eval List(List(:A[1],:B[1],:C[1],:D[1])))[3]

If you have multiple rows, you'd have to decide which row you want the value from, sort the rows first, or use a For Each Row loop.

2 REPLIES
Solution

If your data table has only one row, use the following:

Sort List(Eval List(List(:A[1],:B[1],:C[1],:D[1])))[3]

If you have multiple rows, you'd have to decide which row you want the value from, sort the rows first, or use a For Each Row loop.

dlh

Community Trekker

Joined:

Oct 9, 2012

Great!!!  The trick was adding the Eval List function call as you mentioned. Thanks for your help.