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
    MarkSnodgrass Portland, Oregon 🔴
    Accepted 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.


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

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    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.

  • 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 Indiana 🔴

    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.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

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