group by - aggregation table

[#1]

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.

[jonathandienst · Accepted solution]

Hi

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

[#3]

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?

[jonathandienst]

Hi

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