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.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive