Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
bhanukoppera7491
Contributor II
Contributor II

Sorting multiple string value in a field

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

 

1 Solution

Accepted Solutions
Clever_Anjos
Employee
Employee

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

View solution in original post

11 Replies
avinashelite

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

avinashelite_0-1626096938231.png

 

bhanukoppera7491
Contributor II
Contributor II
Author

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

 

This is single value in a field.

avinashelite

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 

bhanukoppera7491
Contributor II
Contributor II
Author

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"'
];

Clever_Anjos
Employee
Employee

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"'
];

bhanukoppera7491
Contributor II
Contributor II
Author

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.

bhanukoppera7491_0-1626099009746.png

 

Clever_Anjos
Employee
Employee

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

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

bhanukoppera7491
Contributor II
Contributor II
Author

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

Clever_Anjos
Employee
Employee

Your country fields seems to be ok with atomic values