if statement with null values?
I have the below table (the left four columns) and would like to create a new column (called "Differences" as per below table), that is rounded to 3 decimal places
What If Statement can I use ?
I tried Round(if(Col1>0,Database-Col1,Database-Col2),3) ; but it did not work
| Category | Database | Col1 | Col2 | Differences |
| ABC | -0.0018 | -0.0018 | 0.000 | |
| XYZ | 4.49423 | 4.494231854 | 0.000 |
Best Answer
-
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 NeuronOptionsYou could use a visual recipe step to "fold multiple columns" (unpivot) these columns (In your case Col1 & Col2). (Particularly if there are actually more columns in your real dataset that will actually need to be evaluated in order to be successful.) This will create a table that sort of looks like this.
Catagory Database Col_Name Value ABC -0.0018 Col1 -0.0018 XYZ 4.49423 Col 2 4.494231854 From here if there are actually multiple columns that might appear Col1, Col 2, Col 3, Col 4 you need to use a Group by a recipe to come up with an aggregate. Or you are going to do multiple calculations for each category.
However, if there are just two columns and there is only ever one column filled. You could do your calculation simply in a formula step from the data above.
Once this stage is done you could choose to drop the Col_Name Colum in the dataset above. Or even unfold the data back to the original shape. Depends on what your actual data looks like. Lots of opportunities here.
Answers
-
Manuel Alpha Tester, Dataiker Alumni, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Dataiku DSS Adv Designer, Registered Posts: 193 ✭✭✭✭✭✭✭OptionsHow about filling the empty values with zeroes, before the formula step?
If you click on the column name > More Actions > Fill Empty Rows with ...
-
Jurre Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS Core Concepts, Registered, Dataiku DSS Developer, Neuron 2022 Posts: 114 ✭✭✭✭✭✭✭OptionsThe Coalesce-function would be my first choice here, possibly with an extra if-call to check if there are cases where both col1 and col2 have values. Using a temporary column might also be usefull as these combinations of functions can get confusing. "isNull"/"isNotNull" with != or == could do the trick aswell because 0 (zero) is not the same as Null.
edit : i may have been a bit unclear above. Your if-condition checks whether or not a value is bigger than zero, but an empty (Null) value is not the same as zero so that will not work. "isNotNull(col1)" checks for empty values.
The Coalesce-function returns the first non-Null value of a set of values you can specify. You'll find the description of this function under "String-functions" in the formulalanguage-helpsection (https://doc.dataiku.com/dss/8.0/advanced/formula.html). or press "help" -see picture-
Hope this helps, sorry for the late response!
-
Jurre Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS Core Concepts, Registered, Dataiku DSS Developer, Neuron 2022 Posts: 114 ✭✭✭✭✭✭✭Options
Thank you very much for adding your view on this Tom! It turns out that what you describe is a more efficient solution to a problem i faced a while ago with those multiple columns (a lot of them actually).
Life gets easier with neurons around
Would be funny if Dataiku-staff introduced synapses aswell! -
Michael Grayson Administrator, Dataiker, Alpha Tester, Dataiku DSS Core Designer, Community Team Posts: 295 AdministratorOptionsHey @Jurre
- glad you managed to get a great solution!
I suppose the community itself would be the synapses right? Connecting neurons with other neurons (and of course all of our other users!) -
Jurre Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS Core Concepts, Registered, Dataiku DSS Developer, Neuron 2022 Posts: 114 ✭✭✭✭✭✭✭Options
Spot on @MichaelG
! With data and ideas as the neurotransmittersThe funny -and wonderfull- thing is that it was not my question in the first place, i just tried to help @GSung
. @tgb417
stepped in with his take on it what happened to be a great idea which i can use for a completely different -but related- problem. Big smiles all over the place, i can assure you that!