Remap Nulls in Dataset
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
-
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! **0 -
@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! ✔️**0
Answers
-
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! **0 -
@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! ✔️**0
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 303 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 104 SQL DataFlows
- 640 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 767 Beast Mode
- 70 App Studio
- 43 Variables
- 716 Automate
- 185 Apps
- 461 APIs & Domo Developer
- 56 Workflows
- 14 DomoAI
- 39 Predict
- 16 Jupyter Workspaces
- 23 R & Python Tiles
- 402 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 9 Software Integrations
- 134 Manage
- 131 Governance & Security
- 8 Domo Community Gallery
- 44 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive