topic Re: Alphabetically sort characters in a string in QlikView

Alphabetically sort characters in a string

Anonymous — Sun, 04 Jun 2017 11:13:35 GMT

Hi Community.

I need your help. I would like to sort the characters in a string alphabetically in a new field in the load script.

For example:

Myfield Newfield

'abc' 'abc'

'acb' 'abc'

'bca' 'abc'

'zxj' 'jxz'

'yzx' 'xyz'

The Myfield values are unknown so I can't specify various text values to replace in the function.

Any suggestions would be much appreciated.

Re: Alphabetically sort characters in a string

Gysbert_Wassenaar — Sun, 04 Jun 2017 12:35:31 GMT

Here's one way:

MyTable:

LOAD

MyField, Concat(Letter,'',Letter) as NewField

GROUP BY

MyField

;

LOAD

MyField, Mid(MyField,IterNo(),1) as Letter

FROM

...source_table_here...

WHILE

IterNo() <= Len(MyField)

;

Re: Alphabetically sort characters in a string

Anonymous — Sun, 04 Jun 2017 12:57:00 GMT

You are awesome Gysbert, thank you!

RetRoute:

LOAD * INLINE [

Rec, Cus, Br, Rev, Col, Del, MyField

1, 1, 77, 100, HBN, Auck, HBN-Auck

2, 1, 33, 2300, Nel, Has, Nel-Has

3, 1, 33, 290, Has, Auck, Has-Auck

4, 2, 22, 200, Has, Nel, Has-Nel

5, 3, 77, 150, Auck, Has, Auck-Has

6, 3, 88, 50, HBN, Nel, HBN-Nel

7, 3, 77, 300, Nel, HBN, Nel-HBN

8, 3, 77, 800, HBN, Auck, HBN-Auck

9, 5, 44, 350, Auck, Has, Auck-Has

10, 5, 55, 900, Has, Nel, Has-Nel

];

MyTable:

LOAD Rec,

MyField, Concat(Letter,'',Letter) as NewField

GROUP BY

Rec,MyField

;

LOAD Rec,

MyField, Mid(MyField,IterNo(),1) as Letter

Resident RetRoute

WHILE

IterNo() <= Len(MyField)

;

Drop table RetRoute

;

Edit: Script added