ETL Addition empty fields

I am using addition to calculate 2 columns into a calculated column "Total 3".
Example:
Total 1 | Total 2 | Total 3
1 | 2 | 3
2 | 2 | 4
1 | |
4 | 1 | 5
Problem is I don't have data in every row. So as you can see the third one here results nothing instead of 1. 1 + "blank" is still 1.
But it returns an empty field.
Any solution to this in ETL?
Best Answer
-
Hi @user046467
This is because how SQL works. When you add null to something the result is NULL.
A couple of options:
Beast Mode
COALESCE(`Total 1`, 0) + COALESCE(`Total 2`, 0)
MySQL ETL:
Same code above in your select statement
Magic ETL:
Use a Value Mapper tile. Under step 4 - 'Enter a value to search for' click the gear icon and select Null Value. Put 0 in the step 5 - Enter a value to write
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Answers
-
Hi @user046467
This is because how SQL works. When you add null to something the result is NULL.
A couple of options:
Beast Mode
COALESCE(`Total 1`, 0) + COALESCE(`Total 2`, 0)
MySQL ETL:
Same code above in your select statement
Magic ETL:
Use a Value Mapper tile. Under step 4 - 'Enter a value to search for' click the gear icon and select Null Value. Put 0 in the step 5 - Enter a value to write
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Thanks @GrantSmith !
I am new to SQL so figuring everything out by trial and error.
But this makes perfect sense so I will try this soon
0 -
do you actually need to calculate at the row level?
could you just do
SUM(colum1) + sum(column2) and get the desired result?
In beast modes this will be faster performance than calculating at the row level b/c of how adrenaline works.
but if you're concerned that SUM(column1) may end up being NULL, you could wrap it.
coalesce(sum(column1),0) + coalesce(sum(column2),0).
IFNULL may be easier to read but functionally does the same thing. (i prefer coalesce).
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Connect
- 1.3K Connectors
- 309 Workbench
- 7 Cloud Amplifier
- 10 Federated
- Transform
- 664 Datasets
- 120 SQL DataFlows
- 2.3K Magic ETL
- 824 Beast Mode
- Visualize
- 2.6K Charting
- 87 App Studio
- 46 Variables
- Automate
- 195 Apps
- 486 APIs & Domo Developer
- 91 Workflows
- 24 Code Engine
- AI and Machine Learning
- 23 AI Chat
- 4 AI Projects and Models
- 18 Jupyter Workspaces
- Distribute
- 119 Domo Everywhere
- 283 Scheduled Reports
- 11 Software Integrations
- Manage
- 143 Governance & Security
- 11 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 116 Community Announcements
- 5K Archive