Aggregates on data of filtering dimension

MarcelSzimonisz

MVP

27-06-2019

Hello All,

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?

thanks

marcel

Accepted Solutions (1)

Accepted Solutions (1)

asariaaara

01-07-2019

Hi marcel.gent.86​,
That excluding PK option is not found in enrichment from as long i am using ACM.
However you can use query to group/aggregate the data, excluding automatic adding of PK can be found there.

Regards.

Answers (11)

Answers (11)

Amit_Kumar

MVP

03-07-2019

Marcel,

You can use query activity at any place in the workflow by using inbound data.

example:

1783935_pastedImage_1.png

Choose this option.

1783934_pastedImage_0.png

then you can do want ever you want.

solva87

03-07-2019

Hi,

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.

Thanks,

Solon

Amit_Kumar

MVP

02-07-2019

Hello everyone,

I guess, I am bit late for the discussion.

Marcel,

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.

1783517_pastedImage_5.png

Choose following settings:

1783486_pastedImage_4.png

Next choose your filters:

1783518_pastedImage_7.png

final step:

1783519_pastedImage_8.png

That's it

you have some other ways as well but i guess this will solve your issues with enrichment aggregates.

Best Regards,

Amit

MarcelSzimonisz

MVP

27-06-2019

Hello Jon,

that is what I say they removed it from there. Below you can see what we can select now. its either moved or it was removed.

1780894_pastedImage_0.png

summoning the elders Amit_Kumar​,florentlbfloriancourgeydavid_garciaJean-Serge Biron​ for their kind feedback

Marcel

asariaaara

04-07-2019

Hi Amit,


Sorry I didnt catch you.
Are you saying that I should use query activity.

I was asking what should i select here:

1784558_pastedImage_0.png

Since this table is not linked to any other table.

Regards.

asariaaara

03-07-2019

Hi Amit,
Consider I have custom independent table and i want to aggregate/group on same table.
What should i choose in your first step(data linked to filtering dimension)

Regards

MarcelSzimonisz

MVP

03-07-2019

Hello Amit,

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..

MarcelSzimonisz

MVP

01-07-2019

Hello,

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:

  • you do not do aggregates on "Data of the filtering dimension" in the  enrichment activities but rather in the "query" activity..
  • you can do aggregates on  "Data linked to the filtering dimension" on both places

Thanks a lot

wodnicki

MVP

27-06-2019

Hi,

Click 'Advanced parameters...' under 'Edit additional data...' dialog and check 'Disable automatic adding of the primary keys to the targeting dimension'.

Thanks,

-Jon