topic Re: Create a new table, based on aggregations in the original table in QlikView

Create a new table, based on aggregations in the original table

MraakB — Fri, 25 Nov 2022 16:06:10 GMT

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

Re: Create a new table, based on aggregations in the original table

Aditya_Chitale — Sat, 26 Nov 2022 05:16:56 GMT

can you share output for all line items ? It will be easier to understand your requirement in that way.

Regards,

Aditya

Re: Create a new table, based on aggregations in the original table

MraakB — Mon, 28 Nov 2022 08:05:15 GMT

The output should be the last table, so the groupViews per user per date per site, without the individual views. In this case Joe got 2 views from Moe, because they're in the same group and Moe had 2 views in sport section - which is also what Joe is interested in.