Remap Nulls in Dataset

Options
BMilner
BMilner Member

I assume this is an easy one to answer. If I have blanks in one of the columns of my dataset and want to remap these nulls to a certain value, would it continue to remap new nulls in the future to the new values? Or would it only apply to nulls that are present at the time of the remapping?

Basically, our ETL has a formula that resulted in blank values for a few fields. All the values would be the same (this time) so I would like to change all the nulls to the correct value, but this may not be the case going forward.

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓
    Options

    @BMilner If you wrap an IFNULL function around your existing ETL formula, you can specify the value you want nulls to be remapped to like this:

    IFNULL(your formula,'Value for Nulls')
    

    This will apply to all null values every time the ETL runs.

  • ArborRose
    ArborRose Coach
    Answer ✓
    Options

    I would research the coalesce command. Coalesce([fieldname], 0) would give zero if [fieldname] is null.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • david_cunningham
    Answer ✓
    Options

    @BMilner the difference between COALESCE and IFNULL is below for your reference.

    COALESCE: Returns the first non-NULL value in the list of columns, or NULL if there are no non-NULL values.

    IFNULL: If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. You can nest multiple IFNULLs, but usually using coalesce is easier if you want to evaluate multiple columns. IFNULL is great for setting a default value. As @ArborRose mentioned, you can also set a default value with COALESCE().

    For example…

    COALESCE(col1,col2,col3)

    IFNULL(IFNULL(col1,col2),'NO MATCH')

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓
    Options

    @BMilner If you wrap an IFNULL function around your existing ETL formula, you can specify the value you want nulls to be remapped to like this:

    IFNULL(your formula,'Value for Nulls')
    

    This will apply to all null values every time the ETL runs.

  • ArborRose
    ArborRose Coach
    Answer ✓
    Options

    I would research the coalesce command. Coalesce([fieldname], 0) would give zero if [fieldname] is null.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • david_cunningham
    Answer ✓
    Options

    @BMilner the difference between COALESCE and IFNULL is below for your reference.

    COALESCE: Returns the first non-NULL value in the list of columns, or NULL if there are no non-NULL values.

    IFNULL: If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. You can nest multiple IFNULLs, but usually using coalesce is easier if you want to evaluate multiple columns. IFNULL is great for setting a default value. As @ArborRose mentioned, you can also set a default value with COALESCE().

    For example…

    COALESCE(col1,col2,col3)

    IFNULL(IFNULL(col1,col2),'NO MATCH')

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**