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; } } }

How to split multi-word-values and re-arranging them in alphabetical order

ALopez

I have a column with character values that are two letter abbreviations. Some are single values and others are multiple values separated by the delimiter “/”. The problem is that the multi-value values are not in alphabetical order.
I want to iterate through each row and split the multi-values and re arrange them in alphabetical order and insert the new "value" in a New Column. Single values should be just "Copied" and nulls left as nulls.
This is way over my head and I will appreciate any suggestions pointers or reading lists to help me fix this one.

Thanks!

Current data NEW Data
AB AB
CD CD
EF EF
CD/AB AB/CD
null null
EF/AB/CD AB/CD/EF

col = ("Really | Long | and | Boring | Column | Name");

Vals = As Column (col) << Get Values;  // Get Values 

dt = New Column ("NewAbs", Character, "Nominal" );  //New column for NEW DATA
rows = N rows (dt); // Total number of rows to iterate

for(i = 1 , i <= rows, i++,
	if(rowVal (Contains (Vals[i], "/")),  // check if is multi-value i.e. AB/DF
	rowVal = Words(Vals[i],"/");  // Words will split multi-values at "/" 
	sval = Sort List(Eval List(rowVal) );  // sort the character values
	
	For(i = 1, i <= N Items (sVal), i++,
	newVal = sVal[i];
	conCat = Concat Items ( newVal[i] || "/"|| newVal[i+i])
		
	),
	Insert Into (dt, conCat[i]),
		if(rowVal (!Contains (Vals[i], "/")),  // if is NOT multi-value i.e. AB/DF
		Insert Into (dt, rowVal[i])),
	 ),
   );

Accepted answers

txnelson

Here is a formula that works on your sample data table. Attached is a data table with the formula applied

If( Contains( :Current data, "/" ) == 0,
	x = :Current data,
	i = 1;
	theList = {};
	While( Word( i, :Current data, "/" ) != "",
		Insert Into( theList, Word( i, :Current data, "/" ) );
		i++;
	);
	theList = Sort List( theList );
	x = theList[1];
	For( i = 2, i <= N Items( theList ), i++,
		x = x || "/" || theList[i]
	);
)

example.jmp

Jeff_Perkinson

It's not often that I can improve on @txnelson, but I can get it down to a one line formula:

Concat Items( Sort List( Words( :Current data, "/" ) ), "/" )

Words() creates a list of words by breaking the column into words using a "/" as delimiter.

Sort List() sorts the list alphabetically

Concat Items() concatenates the items in the list into a single string using the "/" to separate the items.

2020-11-03_17-28-35.272.png

All comments

txnelson

Here is a formula that works on your sample data table. Attached is a data table with the formula applied

If( Contains( :Current data, "/" ) == 0,
	x = :Current data,
	i = 1;
	theList = {};
	While( Word( i, :Current data, "/" ) != "",
		Insert Into( theList, Word( i, :Current data, "/" ) );
		i++;
	);
	theList = Sort List( theList );
	x = theList[1];
	For( i = 2, i <= N Items( theList ), i++,
		x = x || "/" || theList[i]
	);
)

example.jmp

Jeff_Perkinson

It's not often that I can improve on @txnelson, but I can get it down to a one line formula:

Concat Items( Sort List( Words( :Current data, "/" ) ), "/" )

Words() creates a list of words by breaking the column into words using a "/" as delimiter.

Sort List() sorts the list alphabetically

Concat Items() concatenates the items in the list into a single string using the "/" to separate the items.

2020-11-03_17-28-35.272.png

txnelson

Very, very nice

ALopez

Dear @txnelson and @Jeff_Perkinson Thank you very much for your answers; 0ne a great lesson about loops and the other a mind boggling one-liner.

I will add both to my bag of JSL.

Best regards.

lwx228

In addition to segmentation and sorting, does this formula add and remove the duplicated values after segmentation and keep only the fields that do not repeat?Such as:

Best regards.

txnelson

No, neither solution specified removes duplicates. That requirement was not part of the original request.

The Contains() function has the ability to check across items in a list to see if a match is found. I use it quite often.

Names Default To Here( 1 );
theList = {"AB", "AB", "CD", "EF"};
x = {};
Insert Into( x, theList[1] );
For( i = 2, i <= N Items( theList ), i++,
	If( Contains( x, theList[i] ) == 0,
		Insert Into( x, theList[i] )
	)
);
show(x);

So all that has to be done, is to place this code into one or the other suggested solutions.

x = :Current data;
If( Contains( x, "/" ) > 0,
	theList = Words( x, "/" );
	theList = Sort List( theList );
	x = {};
	Insert Into( x, theList[1] );
	For( i = 2, i <= N Items( theList ), i++,
		If( Contains( x, theList[i] ) == 0,
			Insert Into( x, theList[i] )
		)
	);
	x = Concat Items( x, "/" );
);
x;

Mark_Bailey

This example illustrates another approach using dictionaries:

Names Default To Here( 1 );
theList = {"AB", "AB", "CD", "EF"};

theArray = Associative Array( theList );

theNewList = theArray << Get Keys;

Show( theNewList );