Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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]);
You could do it easily in the chart properties >sort> Text>A-Z
"UK";"India";"Australia";"USA"
This is single value in a field.
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
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"'
];
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"'
];
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.
That´s because your have a comma here in your script
'"India","UK";"America";"Japan"'
I have tried using the same script to load an excel. But unable to get the desired result. Here is the sample input file
Your country fields seems to be ok with atomic values