ETL Addition empty fields

Reply
Highlighted
Yellow Belt

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?


Accepted Solutions
Highlighted
Brown Belt

Re: ETL Addition empty fields

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 the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post


All Replies
Highlighted
Brown Belt

Re: ETL Addition empty fields

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 the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post

Highlighted
Yellow Belt

Re: ETL Addition empty fields

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 Smiley Happy

Highlighted
Major Red Belt

Re: ETL Addition empty fields

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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Announcements
Stand out in the Dojo: Choose your own recognizable username and add a unique profile photo in your profile settings.