Sorting a string with single letters and repeated letters seperated by comma's

[New_Guy]

Hello Guru's

How to sort the below string? that I am getting in a list cell(I am having values like the From String in a column)

From String

B,CC,E,FF,H,II,K,LL,N,OO,Q,T,W,Z

To String

B,E,H,K,N,Q,T,W,Z,CC,FF,II,LL,OO


Basically I have to get all the single letters sorted first followed by repeated letters also sorted. Or can this be done with a custom function at the DB level itself.

Thank you all in Advance
New guy

[AnalyticsWithJay]

Here's the syntax for sorting and re-arranging this list for all letters:

Code Select Expand

#sq(csv(sort(split(',', 'B,CC,E,FF,H,II,K,LL,N,OO,Q,T,W,Z'))))#

To place single letter values ahead of two-letter ones, I would try to use the substitute() macro function if possible, substituting blanks for two-letter values. You'll have to use regular expression patterns to achieve this.

I would then concatenate that string with a second string which substitutes blanks for single-letter values. Unfortunately I don't have the time to experiment with this at the moment, but hopefully it's a good starting point.

Whenever possible, I would always prefer that this is handled by the database rather than Cognos.

[CognosPaul]

It would really be better to do this on the ETL level. There are a few ways you can do it depending on your database. Easiest might be to split it into a subtable and run a CTE on it.

You COULD do it on the fly through Cognos, the same way you COULD push your brand new Porsche down the highway during rush hour - with the parking break engaged.  You could, but why on earth would you? Push back heavily on the requirement, even if you can find the way it'll kill performance and make your friendly neighborhood DBA cry.

[CognosPaul]

Here's the syntax for sorting and re-arranging this list for all letters:

Code Select Expand

#sq(csv(sort(split(',', 'B,CC,E,FF,H,II,K,LL,N,OO,Q,T,W,Z'))))#

To place single letter values ahead of two-letter ones, I would try to use the substitute() macro function if possible, substituting blanks for two-letter values. You'll have to use regular expression patterns to achieve this.

I would then concatenate that string with a second string which substitutes blanks for single-letter values. Unfortunately I don't have the time to experiment with this at the moment, but hopefully it's a good starting point.

Whenever possible, I would always prefer that this is handled by the database rather than Cognos.

I like this approach. If the value is coming from the database, you would need to find a way to get it into the macro. Nest a repeater inside the list and change the macro to:

Code Select Expand

#sq(csv(sort(split(',', prompt('myCSV','token')))))#

Then do a M/D from the list to the parameter in the repeater.

[New_Guy]

Thank you CognoidJay and CognosPaul, I will try the macro approach with the repeater table and i will let you guys know the outcome. But before that I will try to push back on the requirement or will advice to do this in ETL.

New Guy