Grouping using two criteria / subgroups / subtotals

technollygy

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?

MarkSnodgrass

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.

MarkSnodgrass

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.

technollygy

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

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.

technollygy

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

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

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