How to group by several things efficiently

FannyPicamal

Hi all,

I analyse transaction data and I often need to break the metrics by category, while keeping the global value. I want to obtain rows that correspond to the metrics for a precise category of customer (region for example).

Currently I use two different Group recipes to obtain either the values grouped by regions or the global metrics (grouped by nothing) and then stack them. Yet I have sometimes a lot more categories than just regions (channels, brands...) and in these case my previous solution lead to several different group recipes (I believe 2^n group recipes where n is the number of category) which lead to frequent fails in the build of the datasets and a flow difficult to read.

I believe there must be a more efficient way to do the same thing.

I tried using Window recipe but it adds colums and I prefer rows and the Distinct aggregation isn't available (I use it quite often). I also want my individual transaction rows to disappear after I grouped them.

So two questions : Can you see a more efficient way to complete this task with the basic recipes? Can the failed builds originate from something else?

Thank you for your help!

Turribeach

What is your use case exactly? If you are going to show the data in a Dataiku Dashboard then Dataiku Insights might be a better solution.

https://doc.dataiku.com/dss/latest/dashboards/insights/index.html

FannyPicamal

Hi,

I don't use Dataiku for visualisation so I don't use Dataiku dashboards. I only need to share the final table in an excel file.

I start from a transaction table, where each row is a transaction and each transaction is assigned to a few categories (like region, channel, brand...) and have some metrics (amount, number of units purchased...)

In the end, I want a table where the avg/sum of the metrics is computed for each combination of category, so for the transaction from region X, channel Y and brand Z, but also for the transaction from channel Y and any region or brand, and any other combinations.

Currently I use 2^n group recipes where n is the number of categories, which is heavy, and I am looking for a way to compute this final table more efficiently.

I hope this is clearer

Thank you!