topic Sorting based on first digit only in QlikView

Sorting based on first digit only

Thu, 07 Apr 2011 07:39:57 GMT

I have numbers that need to be sorted as follows:
0

001

1
10
1000
2
20

202AC
2000X
etc.

I have tried everything I know,

still i am getting following results

etc

any suggestions?
Thanks,

Sorting based on first digit only

Thu, 07 Apr 2011 07:42:47 GMT

Forgot to mention that This for List box

Sorting based on first digit only

Anonymous — Thu, 07 Apr 2011 07:44:30 GMT

Hm, try sorting the listbox on Expression and use =Text(Fieldname). Hopefully this should give you the desired order.

Sorting based on first digit only

Thu, 07 Apr 2011 08:02:45 GMT

Hi,

Enable the option Load Order- Orginal under Sort tab in list box

Thanks & Regards

Jai

Sorting based on first digit only

Thu, 07 Apr 2011 08:08:55 GMT

HI AMEY, CHECK OUT MY QLIKVIEW FILE.

ONE MORE THING, I DON'T THINK YOU CAN LOAD " 1 " AND " 001" AT THE SAME TIME, BECAUSE THEY ARE VIRTUALLY THE SAME. THANKS.

Sorting based on first digit only

nathanfurby — Thu, 07 Apr 2011 08:55:52 GMT

Take a look at this modified version of the example that Nathanael provided.

Sorting based on first digit only

Thu, 07 Apr 2011 10:42:24 GMT

Hi jai,

Thanks! I have used this option already but it is not giving me any desired results.

I believe this Load Order- Orginal option enables you to get a sort order as per data order in databse.

Sorting based on first digit only

nathanfurby — Thu, 07 Apr 2011 10:46:23 GMT

Yes it will give you the sort order as loaded - my impression was that you wanted to sort by the 1st number in each field value? Which is what the solution I posted does.

EDIT - Ignore above. Just realised you weren't responding to me.

Sorting based on first digit only

Thu, 07 Apr 2011 11:33:10 GMT

Hi Nathan,

Thanks! Using this expression option i got results in some list boxes but still i am facing same problem in few other list boxes.

When there are only numbers in a column or alphanumerics starting with a number as initial digit then only i am getting this required results.

But there are some alphanumerics which are starting with alphabets, in that case if i am applying this LEFT(field,1) expression then these numbers started with alphabet are coming first followed by numbers.

my actual requirement is like a column should get sorted first by numerics and then by alphabtes.

currently i am getting below result: PFA

but i want a result like

012TX
101T
2ABC
559
5594A
559LA
559XY
ABC1
TAB32

Sorting based on first digit only

Anonymous — Thu, 07 Apr 2011 11:58:17 GMT

Can you try loading an expression in the List Box instead of the field directly: =text(YourFieldName) . I think this should give you the desired effect. Let me know if it works;

Cheers,

Dilyana

Sorting based on first digit only

Thu, 07 Apr 2011 12:28:12 GMT

Hi Dilyana,

I have tried this option also[=text(YourFieldName) ] but it is sorting based on text first. I want it to be sorted first by numbers then by text.

Please find an attachment in my earlier post, also at the end i have mentioned the desired result for the column "stat".

Please suggest if u found something useful.............

Thanks!

Sorting based on first digit only

Thu, 07 Apr 2011 12:40:51 GMT

this page intentionally left blank

Sorting based on first digit only

Thu, 07 Apr 2011 13:07:55 GMT

Hi Anders,

I got the exact result! Thanks a lot :-)!

Please explain me how this function works?

Sorting based on first digit only

Thu, 07 Apr 2011 13:10:47 GMT

Hi Dilyana,

i didn't get it at the first place what u asked but that opton works!

Thanks a lot!

Regards,

Amey.

Sorting based on first digit only

Thu, 07 Apr 2011 13:48:49 GMT

I use expression in listbox with =text(stat)

then sort by text.

Regards

Anders

Sorting based on first digit only

Thu, 07 Apr 2011 14:15:01 GMT

Thanks man!

Regards,

Amey.