4236_en-US

liliconcom

Alphabetise or sort cells prior to merge / concatenate<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

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?

Re: Alphabetise or sort cells prior to merge / concatenate

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]

Re: Alphabetise or sort cells prior to merge / concatenate

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]

Re: Alphabetise or sort cells prior to merge / concatenate

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

Re: Alphabetise or sort cells prior to merge / concatenate

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