We have a field that has text and "text numbers" . Any way in a widget to be able to convert the text to a number?
You can have a custom table that converts the field to number, the values that are not possible to be converted are just gonna be empty (null)
select
todouble(quantity)
from (
select 'AA1' as quantity
union all select '29228'
union all select '0001'
union all select 'xxa$%#1'
) dt
Hey,
Try to create custom column for your table and use following formula.
Source: Sisense Mathematical functions
TOBIGINT(string)
Converts a string representing a valid BigInt value to BigInt type.
TODOUBLE(string)
Converts a string representing a valid double value to Double type.
TOINT(string)
Converts a string representing a valid Int value to Int type.
The problem is that in this column there are also text values (poorly normalized table and poorly structured but it is what it is). Could i create a custom column and will it build with the TODOUBLE(string) function even though there may be text that doesn't convert?
Then you can use SUBSTRING to extract number from string.
Formula: SUBSTRING(string,index,length)
Returns the substring of a specified length, starting at a specified index.
sample :
Input string is : ABC123
Formula: TOINT(SUBSTRING('ABC123',3,3)
Output: 123
No, you cannot convert 'ABC123' to Double or Integer.
You can have a custom table that converts the field to number, the values that are not possible to be converted are just gonna be empty (null)
select
todouble(quantity)
from (
select 'AA1' as quantity
union all select '29228'
union all select '0001'
union all select 'xxa$%#1'
) dt
