topic Re: How to split multi-word-values and re-arranging them in alphabetical order in Discussions

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

ALopez — Fri, 09 Jun 2023 23:42:17 GMT

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])),
	 ),
   );

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

txnelson — Tue, 03 Nov 2020 22:13:18 GMT

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]
	);
)

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

Jeff_Perkinson — Tue, 03 Nov 2020 22:29:02 GMT

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.

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

txnelson — Tue, 03 Nov 2020 22:51:36 GMT

Very, very nice

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

ALopez — Wed, 04 Nov 2020 15:43:23 GMT

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.

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

lwx228 — Mon, 09 Nov 2020 08:31:13 GMT

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.

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

txnelson — Mon, 09 Nov 2020 11:07:24 GMT

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;

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

Mark_Bailey — Mon, 09 Nov 2020 13:15:19 GMT

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