Aggregates on data of filtering dimension

[Marcel_Szimonisz]

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

[Jonathon_wodnicki]

Hi,

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

Thanks,

-Jon

[Marcel_Szimonisz]

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.

summoning the elders Amit_Kumar​,florentlb​floriancourgey​ david_garcia​ Jean-Serge Biron​ for their kind feedback

Marcel

[Florian_Courgey]

Hey

I have no idea but I'll follow the thread

Kind regards

[asariaaara · Accepted solution]

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.

[Marcel_Szimonisz]

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

[Amit_Kumar]

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.

Choose following settings:

Next choose your filters:

final step:

That's it

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

Best Regards,

Amit

[Marcel_Szimonisz]

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

[solva87]

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]

Marcel,

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

example:

Choose this option.

then you can do want ever you want.

[asariaaara]

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

[Amit_Kumar]

Query, either at standard or using inbound event data.

[asariaaara]

Hi Amit,

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

I was asking what should i select here:

Since this table is not linked to any other table.

Regards.