Magic ETL - Group By Tile - Sum Multiple Integer Columns

DigitalAviator
edited April 2022 in Magic ETL

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!


Tagged:

Best Answer

  • MarkSnodgrass
    Answer ✓

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • 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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • @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
    Answer ✓

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • @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!

  • 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.

    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"