topic Re: Aggr on Expression in QlikView

Aggr on Expression

Tue, 07 Aug 2012 22:48:23 GMT

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

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!

lironbaram — Wed, 08 Aug 2012 07:49:38 GMT

hi check the attch file

what you where missing is a line id beacuse each order had more then one line

Wed, 08 Aug 2012 12:44:26 GMT

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?

lironbaram — Thu, 09 Aug 2012 05:41:18 GMT

hi check attached