Discussion Board for collaboration on QlikView Layout & Visualizations.
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!);
But in Qlikview I have:
(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!
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
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
try with '' & Field
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 S5INLINE [ Product65A000000RN110GLNS00CN660M0000060];
data:LOAD DISTINCT Product as Prod // it is essential to use different nameRESIDENT sourceORDER BY S1,S2,S3,S4,S5;DROP Table source;
And, sort by load order.