topic group by - aggregation table in QlikView

group by - aggregation table

Wed, 24 Oct 2012 10:45:05 GMT

HI,

I have the following table loaded:

W/C	Medium	Keyword	Visitors	Visits
1	M1	K1	1	1
1	M1	K1	1	1
2	M1	K1	1	1
2	M2	K2	1	1
2	M2	K2	1	1

I would like to have a new table aggregated by W/C, Medium and Keyword which would look like this(Visitors and Visits would use Sum function):

W/C	Medium	Keyword	Visitors	Visits
1	M1	K1	2	2
2	M1	K1	1	1
2	M2	K2	2	2

Anyone could help me to write a script for it, please?

Thanks.

Re: group by - aggregation table

jonathandienst — Wed, 24 Oct 2012 10:51:10 GMT

This script will do that:

LOAD [W/C],

Medium,

Keyword,

Sum(Visitors) As Visitors,

Sum(Visits) As Visits

Resident Table

Group By [W/C], Medium, Keyword

;

Where Table1 is the name of the first table (change as required).

Note that as shown above, you will land up with a synthetic key at the end of the load. The resolution of this will depend on what you are planning to do with the aggregate table.

Are you sure that you want to do this in script? It would be easy enough to calculate these aggregates in a straight table or pivot table.

Hope that helps

Jonathan

Re: group by - aggregation table

Wed, 24 Oct 2012 11:12:21 GMT

Thank Jonathan.

It is very helpful. I think you are right with the calculations in a table rather than script. The end result of the excercise is a chart based on the aggregated values not the aggregated table. Is it possible to do without writing script for it?

Re: group by - aggregation table

jonathandienst — Thu, 25 Oct 2012 11:09:31 GMT

It is possible - to do so, create a straight table or pivot table with dimensions [W/C], Medium, Keyword, and expressions

Sum(Visitors) and Sum(Visits) As Visits.

Regards

Jonathan