topic Re: Sort order in Connectivity & Data Prep

Sort order

hanskaman — Fri, 16 Jul 2021 14:56:04 GMT

Hello,

I have a table that has the following content:

Articlenumber

02010

04405

VE12345

19200

02012HT

I want to sort this on Articlenumber and expect the following order:

02010

02012HT

04405

19200

VE12345

The result that I actually get is:

02012HT

VE12345

19200

04405

02010

What can I do to get the wanted sort order?

Re: Sort order

rubenmarin — Sun, 18 Jul 2021 07:53:56 GMT

Hi, have you tried to load it alphabetically? Select the object, go the properties on the right side, expand sort, disable auto, and disable all options but alphabetical

Re: Sort order

hanskaman — Mon, 19 Jul 2021 06:29:07 GMT

Yes I tried that, no success.

Re: Sort order

MayilVahanan — Mon, 19 Jul 2021 07:22:56 GMT

Hi Hanskaman,

Try with Dual() function if its only few articles and sort based on the number

Re: Sort order

rubenmarin — Mon, 19 Jul 2021 07:25:59 GMT

Hi, I tried and it didn't sort correctly initially because some values were loaded as number and others as text, if you convert all to text (using LOAD Text(Articlenumber) as Articlenumber...) it shows as expected:

Another option can be sorting as expression using the ascii values with an expression like:

Ord(Left(Articlenumber,1))&Ord(Mid(Articlenumber,2,1))&Ord(Mid(Articlenumber,3,1))&Ord(Mid(Articlenumber,4,1))...

Using this kinf of expression will need some adjuntementes if there are lowercase or other symbols in codes,a lso ther is a limit in the length of the number generated.

Re: Sort order

hanskaman — Mon, 19 Jul 2021 11:10:27 GMT

Thank you for your answer. I tried the option with LOAD Text(articlenumber,….)

At first look this was not correct. Then I made a new Visualisation (Table) and suddenly the results are a lot better.

But…. I still have ca. 3 articlenumbers that show up in a strange order:

09810SKL

09815SKL

09825SKL

09828SKL

09830SKL

09855SKL

09860SKL

09870SKL

09880SKL

09895SKL

09895SKLZO

09900HZ

09922HZVZ

09929HZ

09980HZ

09992HZ

19002DIVHT

19055

19065

19075

21200

098260HK

098274HK

098280HK

VE11010BR

VE11015BR

VR09740BR

I expected the yellow marked lines after the red marked line.

Greetings,

Hans Kaman

Re: Sort order

Kushal_Chawda — Mon, 19 Jul 2021 13:01:17 GMT

@hanskaman In table properties, Sorting option for that dimension select sort by measure and put below expression

=rank(Articlenumber)

You can select ascending/descending based on your requirement

Re: Sort order

rubenmarin — Mon, 19 Jul 2021 13:04:03 GMT

I can't tell you why, for me this is a bug, as a workaround you can use both options I proposed:

- 1st sort by expression using (you can add an upper or use a field with all uppercase)

Ord(Left(Articlenumber,1))&Ord(Mid(Articlenumber,2,1))&Ord(Mid(Articlenumber,3,1))&Ord(Mid(Articlenumber,4,1))

- Then also add the option to sort alphabetically