Sorting multiple string value in a field

[bhanukoppera7491]

Hello,

I have been trying to perform a sort inside a value based on ascending order. 

Example:

I have a field name Country and it values are.

Country

"UK";"India";"Australia";"USA"

Expected output is:

"Australia";"India";"UK";"USA"

I have to sort the fields based on A-Z in ascending order. Thank you in advance

[bhanukoppera7491]

Apologies. In the excel I am referring to the field "Active Substance(s)".

[Clever_Anjos · Accepted solution]

Please check if this makes sense to you:

SortedMap:
mapping
Load distinct
[RegPointID],
Concat(distinct [Active Substance],';',[RegPointID]&[Active Substance]) as [Sorted Active Substance(s)]
group by [RegPointID];
load * where len([Active Substance]);
Load
[RegPointID],
trim(SubField([Active Substance(s)],';')) as [Active Substance]
FROM [lib://DataFiles/All Drug in the World (3).xlsx]
(ooxml, embedded labels, table is [All Drug in the World]);

[All Drug in the World]:
LOAD
[RegPointID],
[Country Group(s)],
[Country],
[Product Name (English)],
[Regulatory Status],
[Marketing Status],
[Authorization Number],
[Active Substance(s)],
ApplyMap('SortedMap',[RegPointID]) as [Sorted Active Substance(s)],
[Excipient(s)],
[Active Strength(s)],
[Auth Pharmaceutical Form],
[Route of Administration],
[Marketing Authorisation Holder],
[Application Submitted Date],
Date([Approval Date] ) AS [Approval Date],
Date([Marketed Date] ) AS [Marketed Date],
[No Longer Marketed Date],
Date([End Of Life Date] ) AS [End Of Life Date],
[End of Life Reason],
[Renewal Submitted Date],
[Renewal Approved Date],
[Renewal Required],
[Renewal Due Date],
[Renewal Expiration Date],
[Medicinal Product Type],
[Authorisation Procedure],
[Procedure Number],
[Global Product Category],
[Local Product Category],
[Local Dispensing Class],
[Product Owner],
[Brand Name],
[Formula Number],
[Formula Reference Number]
FROM [lib://DataFiles/All Drug in the World (3).xlsx]
(ooxml, embedded labels, table is [All Drug in the World]);