Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

Aggregates on data of filtering dimension

Avatar

Avatar
Springboard
MVP
MarcelSzimonisz
MVP

Likes

102 likes

Total Posts

225 posts

Correct Reply

56 solutions
Top badges earned
Springboard
Establish
Contributor
Give Back 25
Give Back 10
View profile

Avatar
Springboard
MVP
MarcelSzimonisz
MVP

Likes

102 likes

Total Posts

225 posts

Correct Reply

56 solutions
Top badges earned
Springboard
Establish
Contributor
Give Back 25
Give Back 10
View profile
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)

Avatar

Avatar
Validate 1
Level 3
asariaaara
Level 3

Likes

18 likes

Total Posts

48 posts

Correct Reply

2 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile

Avatar
Validate 1
Level 3
asariaaara
Level 3

Likes

18 likes

Total Posts

48 posts

Correct Reply

2 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile
asariaaara
Level 3

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)

Avatar

Avatar
Springboard
MVP
Amit_Kumar
MVP

Likes

329 likes

Total Posts

649 posts

Correct Reply

231 solutions
Top badges earned
Springboard
Ignite 5
Ignite 3
Ignite 1
Validate 10
View profile

Avatar
Springboard
MVP
Amit_Kumar
MVP

Likes

329 likes

Total Posts

649 posts

Correct Reply

231 solutions
Top badges earned
Springboard
Ignite 5
Ignite 3
Ignite 1
Validate 10
View profile
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.

Avatar

Avatar
Validate 1
Level 2
solva87
Level 2

Likes

6 likes

Total Posts

9 posts

Correct Reply

1 solution
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 1
Affirm 1
View profile

Avatar
Validate 1
Level 2
solva87
Level 2

Likes

6 likes

Total Posts

9 posts

Correct Reply

1 solution
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 1
Affirm 1
View profile
solva87
Level 2

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

Avatar

Avatar
Springboard
MVP
Amit_Kumar
MVP

Likes

329 likes

Total Posts

649 posts

Correct Reply

231 solutions
Top badges earned
Springboard
Ignite 5
Ignite 3
Ignite 1
Validate 10
View profile

Avatar
Springboard
MVP
Amit_Kumar
MVP

Likes

329 likes

Total Posts

649 posts

Correct Reply

231 solutions
Top badges earned
Springboard
Ignite 5
Ignite 3
Ignite 1
Validate 10
View profile
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

Avatar

Avatar
Springboard
MVP
MarcelSzimonisz
MVP

Likes

102 likes

Total Posts

225 posts

Correct Reply

56 solutions
Top badges earned
Springboard
Establish
Contributor
Give Back 25
Give Back 10
View profile

Avatar
Springboard
MVP
MarcelSzimonisz
MVP

Likes

102 likes

Total Posts

225 posts

Correct Reply

56 solutions
Top badges earned
Springboard
Establish
Contributor
Give Back 25
Give Back 10
View profile
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

Avatar

Avatar
Validate 1
Level 3
asariaaara
Level 3

Likes

18 likes

Total Posts

48 posts

Correct Reply

2 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile

Avatar
Validate 1
Level 3
asariaaara
Level 3

Likes

18 likes

Total Posts

48 posts

Correct Reply

2 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile
asariaaara
Level 3

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.

Avatar

Avatar
Springboard
MVP
Amit_Kumar
MVP

Likes

329 likes

Total Posts

649 posts

Correct Reply

231 solutions
Top badges earned
Springboard
Ignite 5
Ignite 3
Ignite 1
Validate 10
View profile

Avatar
Springboard
MVP
Amit_Kumar
MVP

Likes

329 likes

Total Posts

649 posts

Correct Reply

231 solutions
Top badges earned
Springboard
Ignite 5
Ignite 3
Ignite 1
Validate 10
View profile
Amit_Kumar
MVP

03-07-2019

Query, either at standard or using inbound event data.

Avatar

Avatar
Validate 1
Level 3
asariaaara
Level 3

Likes

18 likes

Total Posts

48 posts

Correct Reply

2 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile

Avatar
Validate 1
Level 3
asariaaara
Level 3

Likes

18 likes

Total Posts

48 posts

Correct Reply

2 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile
asariaaara
Level 3

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

Avatar

Avatar
Springboard
MVP
MarcelSzimonisz
MVP

Likes

102 likes

Total Posts

225 posts

Correct Reply

56 solutions
Top badges earned
Springboard
Establish
Contributor
Give Back 25
Give Back 10
View profile

Avatar
Springboard
MVP
MarcelSzimonisz
MVP

Likes

102 likes

Total Posts

225 posts

Correct Reply

56 solutions
Top badges earned
Springboard
Establish
Contributor
Give Back 25
Give Back 10
View profile
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..

Avatar

Avatar
Springboard
MVP
MarcelSzimonisz
MVP

Likes

102 likes

Total Posts

225 posts

Correct Reply

56 solutions
Top badges earned
Springboard
Establish
Contributor
Give Back 25
Give Back 10
View profile

Avatar
Springboard
MVP
MarcelSzimonisz
MVP

Likes

102 likes

Total Posts

225 posts

Correct Reply

56 solutions
Top badges earned
Springboard
Establish
Contributor
Give Back 25
Give Back 10
View profile
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

Avatar

Avatar
Springboard
MVP
Florian_Courgey
MVP

Likes

93 likes

Total Posts

87 posts

Correct Reply

19 solutions
Top badges earned
Springboard
Validate 1
Establish
Contributor
Ignite 1
View profile

Avatar
Springboard
MVP
Florian_Courgey
MVP

Likes

93 likes

Total Posts

87 posts

Correct Reply

19 solutions
Top badges earned
Springboard
Validate 1
Establish
Contributor
Ignite 1
View profile
Florian_Courgey
MVP

01-07-2019

Hey

I have no idea but I'll follow the thread

Kind regards

Avatar

Avatar
Establish
MVP
wodnicki
MVP

Likes

976 likes

Total Posts

1,096 posts

Correct Reply

514 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile

Avatar
Establish
MVP
wodnicki
MVP

Likes

976 likes

Total Posts

1,096 posts

Correct Reply

514 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile
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