Sort by Smallest to Largest Incorrect

[iron-brian]

I have a table where I pulled the numerical part from a text field and used Number() to convert. When I go to Sort and specify Smallest To Largest, it does not work as expected. Seems like a silly issue, but unsure if there is some hiccup I am overlooking with converting text to a number.

What it does → 10, 11, 12, 21, 31, 4, 5, 6
What is expected → 4, 5, 6, 10, 11, 12, 21, 31

[garethbrickman]

Hey iron-brian, sorting on a Number column should definitely yield the numeric order.

To help us to be able to accurately reproduce it, could you please let us know:

• the formula you used to pull the numerical part from a text field
• some examples of the values in that text field
• what cloud data warehouse your data is in as different warehouses may have different behaviors when casting types

You’re also always welcome to pop open a live chat for direct support from within the Sigma app:

[iron-brian]

Hi @garethbrickman , I hope the below details help out.

Number(RegexpExtract([ORIGINAL STRING], "[A-Z]*[-][a-zA-z]?([0-9]*)", 1))

Data is in Snowflake. Though I can find more details if helpful.

Here is an example of the source for the numerical value:

Original String	Resulting Number and Resulting Sort
LM-D1-E-01	1
LM-D10-E-01	10
LM-D11-E-01	11
LM-D12-E-01	12
LM-D2-E-01	2
LM-D3-E-01	3
LM-D4-E-01	4
LM-D5-E-01	5
LM-D6-E-01	6
LM-D7-E-01	7
LM-D8-E-01	8
LM-D9-E-01	9
LM-E1-E-01	1
LM-E10-E-01	10
LM-E11-E-01	11
LM-E12-E-01	12

[garethbrickman]

@iron-brian

Resulting Number and Resulting Sort

Here is my working reproduction, also on Snowflake:

Details of the sort applied:

[iron-brian]

Thanks @garethbrickman! I finally realized my mistake. I was not landing the value in the grouping I was expecting to end up sorted