Remap Nulls in Dataset

BMilner

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.

MichelleH

@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

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

david_cunningham

@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')

MichelleH

@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

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

david_cunningham

@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')