Magic ETL - Group By Tile - Sum Multiple Integer Columns

DigitalAviator

Hi all,

I have a Magic ETL flow going currently where I have three input datasets that I am appending into one dataset. All three datasets have the similar fields and columns, but one of the datasets I need to manually SUM three individual columns across each row to get the "Total" count that exists in the other two datasets as just a single column/field.

• Input Dataset 1: Name |Spend | Inside Leads| Outside Leads | Phone Leads
• Input Dataset 2: Name | Spend |Total Leads
• Input Dataset 3: Name | Cost |Total Leads

• Input Dataset 1 Flow: Input Source -> Select Columns -> Group By -> Add Constant -> Append -> Output Dataset
• Input Dataset 2 Flow: Input Source -> Select Columns -> Add Constant -> Append -> Output Dataset
• Input Dataset 2 Flow: Input Source -> Select Columns -> Add Constant -> Append -> Output Dataset

Output Dataset: Name | Cost | Total Leads

Step 3 "Group by" in my Input Dataset 1 Flow below seems to be where my issue is. If I pull the three individual integer columns into the output dataset, I can aggregate them together just fine with the Step 3 formula below through Beast Mode into a table/chart, but ideally I want the dataset to be fully aligned and pre-aggregated so I dont have an output like the one below:

Output Dataset (do not want): Name | Cost | Total Leads | Phone | Inside | Outside

Any insight is appreciated! Thanks!

MarkSnodgrass

try adding IFNULL() around each of those like this:

IFNULL(Inside Count,0) + IFNULL(Outside Count,0) + IFNULL(Phone Count,0)

If you have blanks that aren't nulls, you may want to do some additional cleanup in a formula tile prior to the group by tile.

MarkSnodgrass

Remove the sum function in your group by statement. The group by tile will sum it for you. You just need it say:

Inside Count + Outside Count + Phone Count

DigitalAviator

@MarkSnodgrass

Thanks for your response! I removed the SUM as suggested but my output set still only shows values in the three individual columns, but the aggregated "Total Leads" column does not contain the summed total of those three fields.

MarkSnodgrass

try adding IFNULL() around each of those like this:

IFNULL(Inside Count,0) + IFNULL(Outside Count,0) + IFNULL(Phone Count,0)

If you have blanks that aren't nulls, you may want to do some additional cleanup in a formula tile prior to the group by tile.

DigitalAviator

@MarkSnodgrass Solution worked!

The two issues i've posted in the Dojo so far have been a result of Null value issues. Maybe I need to take a step back indeed!

Thank you Mark!

jaeW_at_Onyx

Just keep in mind

NULL =/= 0

avg(5 , null, 3) = 4

avg( 5, 0 ,3) = 8/3

wrapping your initial columns in IFNULL( x, 0 ) will skew any average calcs you run on your dataset moving forward.

also, i strongly recommend against preaggregating your datasets in ETL, this might lead to single use datasets that have limited usability. (which in turn means tons of versions of the same dataset potentially pre-aggregated in different ways). just do the aggregation and calculation in a card.