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.
Best Answer
-
Ignacio_Toledo Dataiku DSS Core Designer, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 411 NeuronOptionsHi @tgb417
I just tried this approach, with a find and replace step, and it does work for me:
Hope it helps!
Answers
-
tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,595 NeuronOptions
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.
-
tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,595 NeuronOptions
@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.
-
CoreyS Dataiker Alumni, Dataiku DSS Core Designer, Dataiku DSS Core Concepts, Registered Posts: 1,150 ✭✭✭✭✭✭✭✭✭OptionsBased off of this wonderful thread we have published a Knowledge Base article: Dealing with Accounting-style negative numbers
Thank you both for your help!