Subscribe Bookmark RSS Feed

Alphabetise or sort cells prior to merge / concatenate

johnmeredith

Community Trekker

Joined:

Jan 12, 2012

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?

1 ACCEPTED SOLUTION

Accepted Solutions
ms

Super User

Joined:

Jun 23, 2011

Solution

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]

4 REPLIES
ms

Super User

Joined:

Jun 23, 2011

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

Community Trekker

Joined:

Jan 12, 2012

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

ms

Super User

Joined:

Jun 23, 2011

Solution

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

Community Trekker

Joined:

Jan 12, 2012

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]