topic sort alphabetically characters within a field in QlikView

sort alphabetically characters within a field

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: sort alphabetically characters within a field

sunny_talwar — Tue, 20 Dec 2016 14:04:03 GMT

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

Re: sort alphabetically characters within a field

Tue, 20 Dec 2016 14:10:04 GMT

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

"ADC130" -> "013ACD"

for each occurrence of the field

Re: sort alphabetically characters within a field

adamdavi3s — Tue, 20 Dec 2016 14:30:00 GMT

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

Re: sort alphabetically characters within a field

sunny_talwar — Tue, 20 Dec 2016 14:31:35 GMT

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

May be using SubField() and then Concat()

Re: sort alphabetically characters within a field

adamdavi3s — Tue, 20 Dec 2016 14:32:41 GMT

Sorry yes my misunderstanding of the requirement

Re: sort alphabetically characters within a field

Anonymous — Tue, 20 Dec 2016 14:51:46 GMT

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

Re: sort alphabetically characters within a field

swuehl — Tue, 20 Dec 2016 15:05:53 GMT

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 [

"ADC130"

];

Re: sort alphabetically characters within a field

adamdavi3s — Tue, 20 Dec 2016 15:12:26 GMT

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

Re: sort alphabetically characters within a field

sunny_talwar — Tue, 20 Dec 2016 15:15:12 GMT

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

Re: sort alphabetically characters within a field

aveeeeeee7en — Sat, 11 Apr 2020 00:16:30 GMT

Honestly speaking, this solution is the best & master class.

you're sunny right sunny. But, I've a question here - How does Alpha numeric gets sorted out. Numbers can be sorted in ascending or descending order. But, how to do that with Alphabets. I've tried the solution with customized strings and it's not giving the desired output. I'll try & come back with modified solution.

Sunny, if I'm missing something than please correct me. I came here for the basics & concepts clarity.

Re: sort alphabetically characters within a field

aveeeeeee7en — Sat, 11 Apr 2020 00:17:33 GMT

Perfect Solution & Master Class...........!!

Re: sort alphabetically characters within a field

aveeeeeee7en — Sat, 11 Apr 2020 00:20:00 GMT

Even my brain got jammed... I'm not able to figure out the sorting part that how does it sorted the Alphanumeric string using Order By which is meant for the Number values.