Sort a field alphabetically first, then numerically in current selection

esraltnl — Fri, 28 May 2021 13:44:56 GMT

Hi everyone,

I'm working on QlikSense.I have a dataset like the one below.

data:
LOAD * Inline [
Field
ABCDER
A1GB
BYX
1AHYF
2348
AHJF78
Z6HFPL

];

I want the current selection to be listed as follows. It is enough to consider the first letter. According to the first letter, first alphabetically a..z, then numerically 1..9.

output:

ABCDER
AHJF78
A1GB
Z6HFPL
BYX
1AHYF
2348

How can I do it, can you help?

Re: Sort a field alphabetically first, then numerically in current selection

JuanGerardo — Sun, 30 May 2021 12:19:11 GMT

Hi @esraltnl, this is a bit tricky and I think following solution could work. This is replacing numbers with z character plus the digit to move them to the end of the order, but z characters will be also replaced adding any punctuation sign to keep them before numbers. Something like this:

data: Load Field, Replace(FieldOrder, '9', 'z9') AS FieldOrder; Load Field, Replace(FieldOrder, '8', 'z8') AS FieldOrder; Load Field, Replace(FieldOrder, '7', 'z7') AS FieldOrder; Load Field, Replace(FieldOrder, '6', 'z6') AS FieldOrder; Load Field, Replace(FieldOrder, '5', 'z5') AS FieldOrder; Load Field, Replace(FieldOrder, '4', 'z4') AS FieldOrder; Load Field, Replace(FieldOrder, '3', 'z3') AS FieldOrder; Load Field, Replace(FieldOrder, '2', 'z2') AS FieldOrder; Load Field, Replace(FieldOrder, '1', 'z1') AS FieldOrder; Load Field, Replace(FieldOrder, '0', 'z0') AS FieldOrder; Load Field, Replace(FieldOrder, 'z', 'z.') AS FieldOrder; Load Field, Replace(Field, 'Z', 'Z.') AS FieldOrder; LOAD * Inline [ Field ABCDER A1GB BYX 1AHYF 2348 AHJF78 Z6HFPL ];

I'm not sure if this will cover all the situations, but could be a start point for you:

topic Re: Sort a field alphabetically first, then numerically in current selection in App Development

Sort a field alphabetically first, then numerically in current selection

Re: Sort a field alphabetically first, then numerically in current selection