Sorting Numbers as text

[jpapador]

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?

[datanibbler]

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

[Gysbert_Wassenaar]

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

[jvitantonio]

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

[jpapador]

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?

[vivientexier]

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

[Anonymous]

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

[jpapador]

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.

[swuehl · Accepted solution]

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.