Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
fgirardin
Creator
Creator

Sorting alphanumerical values

Hello,

Using a stored procedure, I create a table to display the whole nomenclature of our products

User can input the main item number and see all sub components + price and qty

if I look for ITEM "H000429", the sorting list is:

H000429110                                      (main product)

H0004291101010                                   (1st item)

H00042911010102020                               (1 - 1st component)

H000429110101020203010                         ( 1 - 1 material)

H00042911010102040                              (1 - 2nd component)

H000429110101020403010                          (1 - 2 material)

H00042911010102050                              (1 - 3rd component)

H000429110101020503010                           (1 - 3 material)

H0004291101020                                   (2nd item)

H00042911010202010                                (2 - 1st component)  

H000429110102020103010                             (2 - 1 material)

H00042911010202020                                 (2 - 2nd component) 

H000429110102020203010                               (2 - 2 material)

H0004291101030                                   (3rd item)

H0004291101040                                   (4th item)

H0004291101050                                   (5th item)

H00042911010502010                                 (5 - material) 

In Excel, I can easily sort the list as shown

In QV, i have some difficulties

I use a pivot table with the sorting numbers as dimension

I tried several options but it always display like this:

 

H000429110
H0004291101010
H0004291101020
H0004291101030
H0004291101040
H0004291101050
H0004291101055
H0004291101060
H0004291101070
H0004291101080
H0004291101090
H00042911010102020
H00042911010102040
H00042911010102050
H00042911010202010
H00042911010202020
H00042911010502010
H00042911010552010
H000429110101020203010
H000429110101020403010
H000429110101020503010
H000429110102020103010
H000429110102020203010

Is there any way to sort the same way as in Excel ?

Thank you for your help

FG

1 Solution

Accepted Solutions
fgirardin
Creator
Creator
Author

The solution was so simple...
I just set my dimension as text

text(sorting)

and that's it...

Sometimes I think too much outside the box

View solution in original post

3 Replies
marcus_sommer

The easiest way to sort these items will be to pre-load them within yor wished order before you load your real data. This means that your script starts (after the main-variables and similar stuff) with something like:

Dummy:

load left(ITEM, index(ITEM, ' ') - 1) as ITEM inline [

ITEM

H000429110                                      (main product)

H0004291101010                                   (1st item)

H00042911010102020                               (1 - 1st component)

H000429110101020203010                         ( 1 - 1 material)

H00042911010102040                              (1 - 2nd component)

H000429110101020403010                          (1 - 2 material)

H00042911010102050                              (1 - 3rd component)

H000429110101020503010                           (1 - 3 material)

H0004291101020                                   (2nd item)

H00042911010202010                                (2 - 1st component)  

H000429110102020103010                             (2 - 1 material)

H00042911010202020                                 (2 - 2nd component) 

H000429110102020203010                               (2 - 2 material)

H0004291101030                                   (3rd item)

H0004291101040                                   (4th item)

H0004291101050                                   (5th item)

H00042911010502010                                 (5 - material)

];

The left() stuff here is only because of the copy from this posting and I think you don't need something like this and just loading these ITEM list from the excel itself.

After all your loadings are finished within the script you used:

drop tables Dummy;

and could then sort ITEM within all objects to the load-order.

In general there are other methods thinkable but all of them need at least the same efforts and more performance and in your case by the length of your ITEM values respectively their number-part will be each other approach not very easy.

- Marcus

fgirardin
Creator
Creator
Author

Hi Marcus,

Thanks for your answer

The values on the left are just there to explain how it sorts the data, it is not a part of the script

In Excel the data look like this

Sorting order                                               Item

  

H000429110H000429
H0004291101010    ETC-253
H00042911010102020         CH-004A
H000429110101020203010             2.0401 Ø25
H00042911010102040         TU-019A
H000429110101020403010             2.0371 Tu Ø15.02 Ø20
H00042911010102050         FO-015A
H000429110101020503010             2.0401 Ø20
H0004291101020    TP-054
H00042911010202010         TI-057
H000429110102020103010             1.4305 Ø 6-f7 Chrome-Dur
H00042911010202020         PI-004A
H000429110102020203010             2.0401 Ø15
H0004291101030    ZJO6013
H0004291101040    BP25 Ø06 x Ø10 x 10
H0004291101050    AM-023A
H00042911010502010         Courbhane Ø15.7
H0004291101055    AM-026A
H00042911010552010         Courbhane Ø15.7
H0004291101060    BN 831 Ø6
H0004291101070    ZJO5014
H0004291101080    ZJO1012
H0004291101090    ZJO1018

The issue with pre loading is that the sorting may vary depending on the main item. So I'm not sure I can create the table as each time it will be different

Thanks for your input though

fgirardin
Creator
Creator
Author

The solution was so simple...
I just set my dimension as text

text(sorting)

and that's it...

Sometimes I think too much outside the box