Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Not applicable

sort alphabetically characters within a field

is it possible, in the process of loading data, sort alphabetically characters within a field (alphanumeric)?

Tags (2)
9 Replies
Highlighted

Re: sort alphabetically characters within a field

Yes, you should be able to use order by to sort a text field

Highlighted
Not applicable

Re: sort alphabetically characters within a field

I apologize for not having properly explained. I would do this:

"ADC130" -> "013ACD"

for each occurrence of the field

Highlighted
adamdavi3s
Honored Contributor

Re: sort alphabetically characters within a field

=keepchar(yourfield,'0123456789')&purgechar(yourfield,'0123456789')

Highlighted

Re: sort alphabetically characters within a field

This alone will not work because ADC -> ACD and 130 -> 013

May be using SubField() and then Concat()

Highlighted
adamdavi3s
Honored Contributor

Re: sort alphabetically characters within a field

Sorry yes my misunderstanding of the requirement

Highlighted
cocosmina
Contributor II

Re: sort alphabetically characters within a field

Hi,

The only solution i see is to make, for each character you have in your field the following function and concatenate, like below:

IF(SubStringCount(Test,IF(SubStringCount(Test,0)<>0,Repeat('1',SubStringCount(Test,1)))1)<>1,Repeat('1',SubStringCount(Test,1)))&...&IF(SubStringCount(Test,'A')<>0,Repeat('A',SubStringCount(Test,'A')))


I can not figure out how to use a loop to not have to insert with concatenation.

Best regards,

Cosmina

Highlighted
MVP
MVP

Re: sort alphabetically characters within a field

Maybe something like

LOAD In, RecId, Concat(Char) as Out

GROUP BY In, RecId;

LOAD *, Recno() as RecId,

  Mid(In,Iterno(),1) as Char

WHILE iterno() <= Len(In);

LOAD * INLINE [

In

"ADC130"

];

Highlighted
adamdavi3s
Honored Contributor

Re: sort alphabetically characters within a field

This is awesome, but how does it sort... I just can't get my brain to process it!

Highlighted

Re: sort alphabetically characters within a field

If you don't provide a sort order to the Concat, it will sort by the ascending order