topic Re: Sorting multiple string value in a field in App Development

Sorting multiple string value in a field

bhanukoppera7491 — Mon, 12 Jul 2021 12:25:56 GMT

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

Re: Sorting multiple string value in a field

avinashelite — Mon, 12 Jul 2021 13:35:56 GMT

You could do it easily in the chart properties >sort> Text>A-Z

Re: Sorting multiple string value in a field

bhanukoppera7491 — Mon, 12 Jul 2021 13:37:47 GMT

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

This is single value in a field.

Re: Sorting multiple string value in a field

avinashelite — Mon, 12 Jul 2021 13:43:48 GMT

This you can't do it directly , first you need to split this field into single values using the subfield() function and again you concatenate the fields after sorting .

Whats the data size , please provide the sample data will help you with the solution

Re: Sorting multiple string value in a field

bhanukoppera7491 — Mon, 12 Jul 2021 13:52:00 GMT

It has thousands of records. I have tried subfield and concat , but the problem I was facing is the country names are repeating multiple times after the concat. Here is the sample data.

LOAD * INLINE [
Country
'"India";"America";"Japan";"UK"'
'"Japan";"UK"'
'"India","UK";"America";"Japan"'
'"America";"Japan";"UK"'
'"Japan";"UK";"America"'
];

Re: Sorting multiple string value in a field

Clever_Anjos — Mon, 12 Jul 2021 13:57:52 GMT

Could you try this?

Load Concat(Country,';',Country) Group by row;
Load recno() as row, SubField(Country,';') as Country;
LOAD * INLINE [
Country
'"India";"America";"Japan";"UK"'
'"Japan";"UK"'
'"India","UK";"America";"Japan"'
'"America";"Japan";"UK"'
'"Japan";"UK";"America"'
];

Re: Sorting multiple string value in a field

bhanukoppera7491 — Mon, 12 Jul 2021 14:10:21 GMT

Thanks for the reply. Below is the screenshot of the output. If you see the first value in the field "UK" is coming before "Japan". But UK should be last as per the order.

Re: Sorting multiple string value in a field

Clever_Anjos — Mon, 12 Jul 2021 14:24:26 GMT

That´s because your have a comma here in your script

'"India","UK";"America";"Japan"'

Re: Sorting multiple string value in a field

bhanukoppera7491 — Tue, 13 Jul 2021 06:49:34 GMT

I have tried using the same script to load an excel. But unable to get the desired result. Here is the sample input file

Re: Sorting multiple string value in a field

Clever_Anjos — Mon, 12 Jul 2021 15:24:08 GMT

Your country fields seems to be ok with atomic values

Re: Sorting multiple string value in a field

bhanukoppera7491 — Mon, 12 Jul 2021 15:26:52 GMT

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

Re: Sorting multiple string value in a field

Clever_Anjos — Mon, 12 Jul 2021 18:35:39 GMT

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