Concatenate word in alphabetical order within a field

[Fiber]

Hi all,

There's a long time since I posted my last question and this community has always been really helpful. I hope to be typing this in the correct board. There I go...

Simple exercise: I have an inline  table with just one field and two rows

tabla:
load * inline [
concept
'|ROJO| |CRIO| |COCHE|'
'|AZUL| |AVE|']
;

My purpose is to get a table with an extra field where the words will be sorted alphabetically

Concept	Concept sorted
|ROJO| |CRIO| |COCHE|	|COCHE| |CRIO| |ROJO|
|AZUL| |AVE|	|AVE| |AZUL|

I've been working with SubStringCount and Subfield formulas unsuccessfully. I suppose the will be a loop at some point, but I've never done it before.

Did anybody prepare something similiar before?

Thanks for your help

Fiber

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

tabla:
LOAD concept,
     Concat('|'&substr&'|',' ') as [concept sorted]
Group By concept;
LOAD *,
     PurgeChar(SubField(concept,'| |'),'|') as substr
INLINE [
    concept
    |ROJO| |CRIO| |COCHE|
    |AZUL| |AVE|
    |ABC| |GHI| |DEF| |JKL|
    |JKL| |DEF| |GHI| |ABC|
    |PQR| |MNO|
    |STU|    
];

hope this helps

Marco

[Fiber]

It's amazing Marco!

Thank you so much for your help. It works!!

Now I have to study how you did it and adapt it to my code, where the real table has +39k rows with over 15k words

Fiber