if statement with null values?

Options
GSung
GSung Registered Posts: 27 ✭✭✭✭

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

CategoryDatabaseCol1Col2Differences
ABC-0.0018-0.0018 0.000
XYZ4.49423 4.4942318540.000
Tagged:

Best Answer

  • tgb417
    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 Neuron
    Answer ✓
    Options

    @GSung

    You 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_NameValue
    ABC-0.0018
    Col1-0.0018
    XYZ4.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
    Manuel Alpha Tester, Dataiker Alumni, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Dataiku DSS Adv Designer, Registered Posts: 193 ✭✭✭✭✭✭✭
    Options

    How 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
    Jurre Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS Core Concepts, Registered, Dataiku DSS Developer, Neuron 2022 Posts: 114 ✭✭✭✭✭✭✭
    Options

    The 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
    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
    Michael Grayson Administrator, Dataiker, Alpha Tester, Dataiku DSS Core Designer, Community Team Posts: 294 Administrator
    Options

    Hey @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
    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 neurotransmitters

    The 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!

Setup Info
    Tags
      Help me…