Typology rule - get recipients who have received 'n' deliveries | Community
Skip to main content
campaignap
campaignap
Level 3
December 13, 2021
Solved

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

  • December 13, 2021
  • 2 replies
  • 962 views

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

 

 

 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Marcel_Szimonisz

Hello @campaignap ,

You will need to set your query as follows

First screen:

Select filtering condition:

How you do it?

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

 

  • will get you something like

 

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

 

  • change the operator and right hand side of the condition

  • 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

 

  • 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

2 replies

AksheyMalhotra
AksheyMalhotra
Level 2
December 13, 2021

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

    Marcel_Szimonisz
    Community Advisor
    Marcel_SzimoniszCommunity AdvisorAccepted solution
    Community Advisor
    December 20, 2021

    Hello @campaignap ,

    You will need to set your query as follows

    First screen:

    Select filtering condition:

    How you do it?

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

     

    • will get you something like

     

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

     

    • change the operator and right hand side of the condition

    • 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

     

    • 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

      Terms & ConditionsAccessibility statement

      Loading footer...