topic Sorting alphanumerical values in QlikView

Sorting alphanumerical values

fgirardin — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Sorting alphanumerical values

marcus_sommer — Thu, 23 Aug 2018 08:31:51 GMT

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

Re: Sorting alphanumerical values

fgirardin — Thu, 23 Aug 2018 09:26:54 GMT

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

Re: Sorting alphanumerical values

fgirardin — Thu, 23 Aug 2018 09:54:13 GMT

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