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
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:
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:
||Resulting Number and Resulting Sort
Here is my working reproduction, also on Snowflake:
Details of the sort applied:
Thanks @garethbrickman! I finally realized my mistake. I was not landing the value in the grouping I was expecting to end up sorted