Different types of decimal types in dataset

[MaartenDB]

Hi all

the problem that I have is that our data is from different countries. Which means that some people will use a '.' or a ',' as a decimal type. But Datasphere cannot work with both at the same time. Is there anyway I can fix this? This field could also be left empty or '0'. I already tried to make something (see pictures) but it still filters out some data.

Thanks for the help!

[Chris_Tam · Accepted solution]

I assume this is a string that you are trying to act on? If so, perhaps one way is to check what separator is used for the decimal place and act accordingly? For example assuming the column name is "Amount", you can create a formula to make that into decimal:

case when Amount is null OR Amount = '0' then 0 
when SUBSTRING(Amount,LENGTH(Amount)-2,1) = ',' THEN  TO_DECIMAL(REPLACE(REPLACE(Amount,'.',''),',','.'))
when SUBSTRING(Amount,LENGTH(Amount)-2,1) = '.' THEN TO_DECIMAL(REPLACE(Amount,',','')) end

1) checks all the empty and 0s

2) looks for the decimal separator and when it is a comma, remove all the periods as those are the thousands separator, then change the decimal separator to a period

3) looks for the decimal separator and when it s a period, just remove all the commas as those are the thousands separator