Discover the winners & finalists of the 2022 Dataiku Frontrunner Awards!READ THEIR USE CASES

Dealing with Negative numbers that use ()

Solved!
tgb417
Dealing with Negative numbers that use ()

I have a dataset coming in with negative numbers being represented in an accounting fashion with leading and trailing parentheses.  For example (200) equals -200.

Anyone have a quick way to clean that up in a visual recipie?

The Extract Number Processor does not resolve this issue.

I'm using DSS 8.0.4.

--Tom
0 Kudos
1 Solution

Hi @tgb417 

I just tried this approach, with a find and replace step, and it does work for me:

idea.png

Hope it helps!

View solution in original post

4 Replies
tgb417
Author

I ended up writing this complicated formula.

if(contains(strval("Sum of Total Received"), '('), 
concat("-",substring(strval("Sum of Total Received"),1,length(strval("Sum of Total Received"))-1)),
numval("Sum of Total Received"))

However, that is sort of a pain.  Anyone have a faster way to deal with this challange.

 

 

 

--Tom
0 Kudos

Hi @tgb417 

I just tried this approach, with a find and replace step, and it does work for me:

idea.png

Hope it helps!

tgb417
Author

@Ignacio_Toledo, This is good, you can do this in just one recipe step.  My approach took 4 steps per column to get the names of columns back the way they were using my method.

And even better you can do this on multiple columns with the same step.

Bravo!

I've tested and can reproduce your approach.

 

--Tom
CoreyS
Dataiker Alumni

Based off of this wonderful thread we have published a Knowledge Base article: Dealing with Accounting-style negative numbers

Thank you both for your help!

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!