topic Sorting Numbers as text in QlikView

Sorting Numbers as text

jpapador — Tue, 08 Oct 2013 14:55:12 GMT

I have a product number field that is alpha numeric that I would like to sort as text. For example,

Currently product 10000 would come after product 1111 because it is a larger number. On the sort tab I am using sort alphabetically A --> Z and the product number field is stored as text.

Ideally I would like Qlikview to read the numbers as letters meaning:

0 = A

1 = B

2 = C

3 = D

and so on...

Has anyone done this?

Re: Sorting Numbers as text

datanibbler — Tue, 08 Oct 2013 15:01:55 GMT

Hi jpapador,

you could use a mapping table (with two fields, mapping the numbers against letters just like you've done here) and use the APPLYMAP() function to create a "sorting_field".

HTH

Best regards,

DataNibbler

Re: Sorting Numbers as text

Gysbert_Wassenaar — Tue, 08 Oct 2013 16:26:10 GMT

Try sorting by expression and use as expression something like MyField&Repeat('0',10-len(MyField)). Replace MyField with the name of your field.

Re: Sorting Numbers as text

jvitantonio — Tue, 08 Oct 2013 16:37:02 GMT

Hi, go to the sort tab, check Text and select Z -> A and see if that works. J

Re: Sorting Numbers as text

jpapador — Tue, 08 Oct 2013 22:19:49 GMT

Since the product numbers aren't single numbers they are like 1111, where could I apply the map so that it reads 1111 as aaaa?

Re: Sorting Numbers as text

vivientexier — Tue, 08 Oct 2013 22:34:20 GMT

You should use the Dual() function. This is the kind of function used to tell QlikView that "may" should be the 5th month.

Re: Sorting Numbers as text

Anonymous — Wed, 09 Oct 2013 00:17:10 GMT

Hello Jappador,

I think there is 2 ways to do that:

1 ) Create a Expression Text( Field ) and order by A -> Z

2 ) Sort by expression. Text( Field ).

They show diferents results, but the 10000 comes before 1111 in both approaches. See results and attached, please.

Regards

Re: Sorting Numbers as text

jpapador — Wed, 09 Oct 2013 16:33:35 GMT

I still cannot get it just right... I have attached a sample QVW. As you can see I created a field where I replaced the product numbers with letters and the sort works if I create a table box with both fields and sort on the new field created. The problem comes in when you just try to sort product numbers without the field in the same table. I can't figure out how to write the expression.

Re: Re: Sorting Numbers as text

swuehl — Wed, 09 Oct 2013 16:45:25 GMT

Maybe like attached. Sort your field in load order (like in the sample file's list box) and compare to your table sorted by the other field.