nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Alphabetise or sort cells prior to merge / concatenate

johnmeredith

Hi there,
Having searched and searched through the JMP manual I cannot seem to find a solution to my issue which I am sure is straight forward.

I have several columns which all contain data which is similar based on specific codes. For example:

Code 1 Code 2
K26.3 U20.2
K26.3
K26.2
K26.2 U20.2
U20.2 K26.2
U20.2 K26.2
K26.2
U20.2 K26.3
K26.2 U20.2
U20.2 K26.2
U20.2 K26.2
U20.2 K26.2
K26.2 K33.9
U20.2 K26.2

The end result I am after will sort these codes alphabetically, and then concatenate to create a new code which can then be used for statistical analysis. Think of it as a sorted or alphabetised group.

e.g. the first code could result in "K26.3-U20.2", the second would just be "K26.3" which the last one would be "K26.2-U20.2".
Any ideas how I can do this in JMP?

Accepted answers

Yes it makes it easier, but both above formulas should work.

Without the hyphen this column formula works:

sortlist(evallist({:Code 1,:Code 2}))[1]||sortlist(evallist({:Code 1,:Code 2}))[2]

All comments

If I understand the problem right it's not very straight forward as the order matters as well as empty cells must not have the hyphen "-". So a simple concatenate formula without conditional statement wont do.

I think there are several solutions (as often in JMP) to this. Here are two different approaches using a column formula. The first formula is based on conditinal simple conditionals, the second creates a sorted list and use regular expressions to delete any redundant "-".

To try, run this script or just paste the expression inside Fromula() into a the formula property of a new column.

current data table()<<new column("Code 1-Code 2",formula(If( :Code 1 > :Code 2,

:Code 2 || If( Is Missing( :Code 2 ), "", "-" ) || :Code 1,

:Code 1 || If( Is Missing( :Code 1 ), "", "-" ) || :Code 2

)));

current data table()<<new column("Code 1-Code 2",formula(

sl = sortlist(evallist({:Code 1,:Code 2}));

regex(sl[1]||"-"||sl[2],"^-*(.+)-*$","\1")

))

Message was edited by: Marcus Sundbom
Added second formula

johnmeredith

If it makes it easier, the hyphen can be excluded - that is only there to make it easier to split the codes up afterwards.

Yes it makes it easier, but both above formulas should work.

Without the hyphen this column formula works:

sortlist(evallist({:Code 1,:Code 2}))[1]||sortlist(evallist({:Code 1,:Code 2}))[2]

johnmeredith

That is perfect. I extrapolated the formula out to include additional codes and it works a charm! Many thanks!

sortlist(evallist({:Code 1,:Code 2,:Code 3,:Code 4,:Code 5}))[1]||sortlist(evallist({:Code 1,:Code 2,:Code 3,:Code 4,:Code 5}))[2]||sortlist(evallist({:Code 1,:Code 2,:Code 3,:Code 4,:Code 5}))[3]||sortlist(evallist({:Code 1,:Code 2,:Code 3,:Code 4,:Code 5}))[4]||sortlist(evallist({:Code 1,:Code 2,:Code 3,:Code 4,:Code 5}))[5]