Dealing with Accounting-style negative numbers

In Accounting, it is common to represent negative numbers with leading and trailing parentheses. For example, (200) equals -200.

A “Find and replace” step in the Prepare recipe can transform a string column with values like (200) into a numeric column with values like -200.

Find and Replace Step

In a “Find a replace” step,

  • Choose the column with the accounting-style values.

  • Replace the open parenthesis with a hyphen (minus sign).

  • Replace the closed parenthesis with no value.

../../../_images/accounting-find-replace.png

Note

The storage type of the original column is a string, but the newly-created column is a double.

In this example, only one column is being converted. Changing the Column setting from “single” to “multiple” would allow you to convert multiple columns in the same step.

What’s next?

There are a number of places you can learn more about data preparation in Dataiku DSS.