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
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Typology rule - get recipients who have received 'n' deliveries

Avatar

Level 2

How can we create a filter typology rule to exclude recipients that have received less or more than 'n' deliveries?

 

campaignap_1-1639368801345.png

campaignap_2-1639369095760.png

 

 

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hello @campaignap ,

You will need to set your query as follows

First screen:

Marcel_Szimonisz_0-1640036900888.png

Select filtering condition:

Marcel_Szimonisz_1-1640036956607.png

How you do it?

  • Click on select field and find the link recipient broad log

Marcel_Szimonisz_2-1640037009826.png

 

  • will get you something like

Marcel_Szimonisz_3-1640037044267.png

 

  • double click on the expression and add any aggregation you like. In this case we add COUNT

Marcel_Szimonisz_4-1640037112942.png

 

  • change the operator and right hand side of the condition

Marcel_Szimonisz_5-1640037157978.png

  • add more conditions  to your selection. You can add time restrictions as well as status restrictions. Or if not needed remove the empty line or click on finish

Marcel_Szimonisz_6-1640037197645.png

 

  • See the preview or double check the SQL if you have  got what you wanted (if you feel like so)

 

SELECT   R0.iRecipientId FROM NmsRecipient R0 WHERE (SELECT   COUNT(*) FROM NmsBroadLogRcp B1 WHERE (B1.iRecipientId = R0.iRecipientId) AND (((B1.iStatus = 1)))) < 5 LIMIT 10001
  • ...

 

 

  • profit

 

Marcel

View solution in original post

0 Replies

Avatar

Level 2

Hi,

 

Hope you are doing well.

 

You could query the Brodlog table and see in how many deliveries a particular recipient is targeted, the same could be done on profile schema as well.

 

Regards,

Akshey Malhotra

Avatar

Correct answer by
Community Advisor

Hello @campaignap ,

You will need to set your query as follows

First screen:

Marcel_Szimonisz_0-1640036900888.png

Select filtering condition:

Marcel_Szimonisz_1-1640036956607.png

How you do it?

  • Click on select field and find the link recipient broad log

Marcel_Szimonisz_2-1640037009826.png

 

  • will get you something like

Marcel_Szimonisz_3-1640037044267.png

 

  • double click on the expression and add any aggregation you like. In this case we add COUNT

Marcel_Szimonisz_4-1640037112942.png

 

  • change the operator and right hand side of the condition

Marcel_Szimonisz_5-1640037157978.png

  • add more conditions  to your selection. You can add time restrictions as well as status restrictions. Or if not needed remove the empty line or click on finish

Marcel_Szimonisz_6-1640037197645.png

 

  • See the preview or double check the SQL if you have  got what you wanted (if you feel like so)

 

SELECT   R0.iRecipientId FROM NmsRecipient R0 WHERE (SELECT   COUNT(*) FROM NmsBroadLogRcp B1 WHERE (B1.iRecipientId = R0.iRecipientId) AND (((B1.iStatus = 1)))) < 5 LIMIT 10001
  • ...

 

 

  • profit

 

Marcel