Scientific Notation not removed by format() function

Joeiku
Level 2
Scientific Notation not removed by format() function

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

0 Kudos
5 Replies
Makoto
Dataiker

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)

 

Screenshot 2020-11-23 at 13.30.27.png

 

 

 

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
Level 1

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. 

0 Kudos
CoreyS
Dataiker Alumni

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

Looking for more resources to help you use Dataiku effectively and upskill your knowledge? Check out these great resources: Dataiku Academy | Documentation | Knowledge Base

A reply answered your question? Mark as โ€˜Accepted Solutionโ€™ to help others like you!
0 Kudos
RohitUpadhyay
Level 1

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

0 Kudos
ktgross15
Dataiker

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

0 Kudos