Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Error in table sorting

Hi guys,

i have a problem in table sorting.

I have a linear table where are listed product codes.

Product codes consist of both numbers and letters.

I've tried to sort by alphabetical or numerical order but i obtain the same result and i think there is a mistake.

For example, i've 3 product codes.

Product 1 -> 65A000000RN;

Product 2 -> 110GLNS00CN;

Product 3 -> 660M0000060.


I expect that correct order would be:

Product 2 -> 110GLNS00CN (first, because it starts with 1!);

Product 1 -> 65A000000RN (65 comes before 66!);

Product 3 -> 660M0000060.


But in Qlikview I have:

Product 1;

Product 2;

Product 3.

(seen the attached image)


I think that Qlikview puts product 1 before product 2/3 because the product 1 code has a letter in third position, while the others has a letter in fourth position (just my idea!).


How can i obtain a "correct" order?

Thanks in advance!


product list.png



1 Solution

Accepted Solutions
Highlighted
Master II
Master II

Try using text(Product_Code) for the dimension, or using Sort by Expression and putting text(Product_Code) as the expression.

View solution in original post

4 Replies
Highlighted
Master II
Master II

Try using text(Product_Code) for the dimension, or using Sort by Expression and putting text(Product_Code) as the expression.

View solution in original post

Highlighted
Not applicable

Hi Michael

what you can do is to sort in the script

LOAD text(prodcut_code)     as product_code, ..... FROM table

you can then sort either by text or by load order

best regards

Chris

Highlighted
MVP
MVP

try with '' & Field

sort 106110.jpg

Highlighted
Champion III
Champion III

This works (using first five characters here for sorting).  I hope there is a better way:

LOAD Product,
mid(Product,1,1) as S1,
mid(Product,2,1) as S2,
mid(Product,3,1) as S3,
mid(Product,4,1) as S4,
mid(Product,5,1) as S5
INLINE [
Product
65A000000RN
110GLNS00CN
660M0000060]
;

data:
LOAD DISTINCT Product as Prod // it is essential to use different name
RESIDENT source
ORDER BY S1,S2,S3,S4,S5;
DROP Table source;

And, sort by load order.