Before there was a checkbox where you could exclude id from the main set when doing aggregate functions. Now that is gone you always get error saying
enrich WDB-200001 SQL statement 'INSERT INTO wkf631156212_14_1 (sBrowserName,iMax__id_,iCount__id_,iId) SELECT U2.sBrowserName, max(W0.iId), Count(W0.iId), W0.iId FROM wkf631156212_13_1 W0 JOIN NmsTrackingLogRcp T1 ON (T1.iTrackingLogId = W0.iId) JOIN NmsUserAgent U2 ON (U2.iHashKey = T1.iUserAgent) WHERE ((T1.iTrackingLogId > 0 OR T1.iTrackingLogId < 0)) GROUP BY U2.sBrowserName' could not be executed.
enrich PGS-220000 PostgreSQL error: ERROR: column "w0.iid" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...422,iId) SELECT U2.sBrowserName, count(W0.iId), W0.iId FRO...
It always add the primary key to the aggregate function how i am supposed to do the queries now. Do you guys have workaround other than select all and then do the aggregates linked to the dimension?
I'm 99% sure that option was never available in an enrichment activity
The way i usually do it, is i use a query to select the data and fields i need to aggregate, disable the primary key and then do the actual aggregation and grouping in an enrichment. After that, if i need to do any joins with any base tables (e.x recipient table), i query that table separately and bring both transitions into a new enrichment, and then do a join using Add Data -> Create a link in the Enrichment.
I guess, I am bit late for the discussion.
Can you share the use case? Enrichment ass name suggests used to enrich data for target dimensions element. i.e. add additional information for selected recipients. whenever we use enrichment to add aggregates data it's only mean to add aggregated date from 1-n dimensions. Typical examples, count of number of clicks for a user grouped by browser names, count of number of opens/clicks for a user grouped by device type.
In your case you should go in reverse way to get your requirement i.e use change dimension to go to browser(user agent dimension) then in enrichment follow these steps:
Add data, choose data linked to the filtering dimensions.
Choose following settings:
Next choose your filters:
you have some other ways as well but i guess this will solve your issues with enrichment aggregates.
i was trying to do aggregates in enrichment of the data of the filtering dimension. it let me do it.. but it keep on adding primary key to the selected fields.. and throwing errors that the primary key has to be part of aggregation query.
I always thought that you can use checkbox in advanced parameters to remove the pk from the targeting dimension also in enrichment but apparently it was never there
So you need to do either what you have mentioned in your comment or do it directly in the query.. which not all the time you can..
yes in the query we have this option.. to be honest I don't remember if enrichment had it.. but it is really misleading because it let you do the aggregate functions but then throwing this error you cannot get rid of..
So the key learnings are:
Thanks a lot