Scientific Notation not removed by format() function

Joeiku

Hi there,

I currently have a value in one of my columns which reads: -2.1884462822743185E7

I am trying to get it into the format: -21884462.82

I have seen that I'm not the only user to have this problem, and other users have been recommended to use the formula:

format("%.2f", your_column_name)

However, this only works in the sample output next to the formula box, and not in the actual output.

Any Ideas?

Kind regards,

Joe

Makoto

Hello Joe,

By default, DSS will display numbers in scientific notation above a certain threshold and unfortunately this is not configurable.

If you do need to keep the value, your workaround would be to multiply this column by 10 and apply the formula below(3f instead of 2f).

format("%.3f", your_column_name)

Instead of applying formula, you can also use round number processor in the Prepare recipe.

Another (less recommended than above) workaround would be to store this column in a string format.

Best regards,

Makoto

ecathell

Using this method does not allow the prepare recipe to be run in-database and must use DSS as the processor. I don't feel this is an acceptable solution for processing large datasets of data.

CoreyS

Hi @ecathell
I sent you a private message. I'm looking forward to hearing back from you soon!

RohitUpadhyay

This does not work.It only works when you create recipe.And once you create the output dataset it rolls back to scientific notaion.

Katie

Hi all,

I hear your frustration. The knowledge base article has actually been updated to specify that the storage type would need to be changed to string in the output dataset in order for the regular number format to persist visually (otherwise you only see within the prepare recipe). I'm aware that this is definitely not an ideal solution, but unfortunately that's the best solution now.

Feel free to submit a feature request on this topic here so that the product team is aware of your interest in changing the default numerical format from scientific notation to standard number format.

Katie