Concatinate aggregated values

[hiringel]

Hi,

In my transformation layer, I am trying to create a new country with values from the other countries.

CustomerRiskTable:

YearMonth	Country	Customers	Risk
201701	Norway	20168	20%
201702	Norway	20178	21%
201703	Norway	20190	19%
201701	Sweden	11156	15%
201702	Sweden	11160	16%
201703	Sweden	11200	17%
201701	Denmark	5536	5%
201702	Denmark	5520	4%
201703	Denmark	5520	8%
201701	Finland	55230	50%
201702	Finland	55600	51%
201703	Finland	56800	60%

I want to add 1 row for each YearMonth in this table, customers should be sum of the others, risk should be the average weighted by number of customers.

In pseudocode (not really code at all):

Concatenate CustomerRiskTable:

For each i in YearMonth;

i as YearMonth,

{'Nordic'} as Country,

sum(Customers) as Customers,

weightedAVG(Countries,Customers) as Risk

So it looks a little bit like this:

YearMonth	Country	Customers	Risk
...	...	....	...
201701	Nordic	92090	0,364847
201702	Nordic	92458	0,374222
201703	Nordic	93710	0,429641

Can anyone help me with this?

[sunny_talwar · Accepted solution]

May be this

Concatenate (CustomerRiskTable)

LOAD YearMonth,

     'Nordic' as Country,

     Sum(Customers) as Customers,

     Sum(Customers*Risk)/Sum(Customers) as Risk

Resident CustomerRiskTable

Group By YearMonth;