Grouping using two criteria / subgroups / subtotals

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

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

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

    Resulting in...


  • 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"
  • 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?

  • 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!**
  • 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"