Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, Developers.
I need some help with Aggr on Expression.
I'm trying to get counts from multiple columns and group them by the max of the count.
Here is what I'm trying to do
Order # | Item 1 | Item 2 | Item 3 | Item 4 |
---|---|---|---|---|
1 | Box Blue | |||
1 | Box Blue | Box Red | ||
2 | Cup Blue | Cup Red | Cup Green | |
2 | Cup Blue |
Then the count of each order will look like this
Order | Count |
---|---|
1 | 1 |
1 | 2 |
2 | 3 |
2 | 1 |
Now.. I want to make it like
Order | Count |
---|---|
1 | 2 |
2 | 3 |
So I used in Straight Table:
AGGR(MAX(
COUNT(DISTINCT Item1) + COUNT(DISTINCT Item2) + COUNT(DISTINCT Item3) +
COUNT(DISTINCT Item4))
, Order_ID
1) Am I using right function and right syntax (i know at least 2nd part is not right)
2) Can you help me to correct this?
Thanks!
hi check the attch file
what you where missing is a line id beacuse each order had more then one line
Thank you for your help.
I have one more question though.
Your aggregation makes perfect sense, but is there a way to display the aggregated # and the items of the max of the aggregation.
For example,
From the example top, can I do this? (From your attachment)
Order # | Item 1 | Item 2 | Item 3 | Item 4 | Count (Aggr) |
---|---|---|---|---|---|
1 | Box Blue | Box Red | - | - | 2 (Max of Aggr) |
2 | Cup Blue | Cup Red | Cup Green | - | 3 |
So basically, while getting the count also get the items of the rows with largest count value.
Do I need different function for this?
hi check attached