Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
H000429110 | H000429 |
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
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