Grouping using two criteria / subgroups / subtotals

Options
technollygy
technollygy Member
in Magic ETL

I have a dataset that works a bit like my first picture here:

20210708 213148.png

What I need is an output that looks something like this:

20210708 213533.png

By that, I mean that I don't simply want a total of how many drinks were sold overall by each person, or how many of each drink were sold (which I could do with the group function in Magic ETL). I need to group according to the two criteria.

Can anyone suggest what I'd have to do in Magic ETL to achieve this?

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    You can do this with a Group By tile and have your name and drink in the select list and then use Sum or Count as the aggregate on the field you want to total.

    **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

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    You can do this with a Group By tile and have your name and drink in the select list and then use Sum or Count as the aggregate on the field you want to total.

    **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.
  • technollygy
    technollygy Member

    All it was is that I didn't realise that you could select two columns as the grouping! Thanks - I just needed someone to make it really obvious. For the benefit of anyone else:

    20210708 232213.png

    Resulting in...

    20210708 232313.png


  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    Be advised, in your viz you're showing Josef / Milk = 0.

    A GROUP BY tile won't show the empty spaces, it will only show where data exists.

    To include NULL spaces you'd have to include the universe of combinations.

    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"
  • technollygy
    technollygy Member

    Thanks for that. Yes, that's ok. I'm fine with that.

    On a related note, when I do need to replace null values with 0, I currently have to convert the column type to text, replace null with 0, then convert it back to integer. I'm sure there must be a more sensible way. Any ideas?

  • GrantSmith
    GrantSmith Coach

    Hi @technollygy

    the Magic ETL 2.0 Beta has a formula tile where you can just do a simple COALESCE function to default the values. The way you have it is the only real way to do it in Magic ETL 1.0.

    It's been in beta for a while. Talk with your CSM to see if you can be apart of the beta or you can wait until it's in GA.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    when you ingest the data into Magic 2 (input tile) you can set NULL handling behavior.

    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"

Categories