From the first table I want to produce the result that is in the last table. So in this example only one record needs to be created. The logic is as follows. Users are in the userGroups and users have interest in specific "sites". If any user from a userGroup visits the site, that view is counted for him, but also for all the other users from the same userGroup that have interest in that site.
groupViews in the final table is the sum of all the views from all users in the group that have views for one of the sites of the user
VIEWS:
Load * Inline [
date, user, userGroup, site, views
1, moe, a, sport, 2
1, moe, a, art, 2
1, bo, b, sport, 2
];
USERS_SITES:
Load * Inline [
user, userSite
joe, sport
moe, sport
moe, art
bo, sport
];
// Desired result:
GROUP_VIEWS:
Load * Inline [
date, user, userGroup, site, groupViews
1, joe, a, sport, 2
];
