0

I have three data extensions: Member Info, Policy Info, and Event.

Member and Policy DE's are related on a field called Account ID. The Policy and Event DE's are related on a field called Policy Number.

Is it possible to set up a filter (or something similar) to have the Event DE find a match in the Policy DE and then have that map back to the Account ID in the Member DE?

1

Yes. You can join them with a Query Activity:

select
e.[event name] /* or whatever */
, p.[policy number]
, m.[account id]
from [event] e 
left join [policy info] p on (p.[policy number] = e.[policy number])
left join [member info] m on (m.[account id] = e.[account id])

Of course, the target data extension's primary key will need to accommodate the one-to-many resulting from the joins.

You can switch to inner joins instead of left joins if you'd like to include only events with a policy and member.

  • Can you clarify what you mean by accomodating one-to-many? Is this something that I need to manually turn on? Additionally, do I need to create a new, separate data extension to house the results of the Query Activity? – jessed Jul 5 '17 at 13:27
  • Regarding the one-to-many -- is it possible for an event to have more than one policy or more than one member? If so, then the primary key should allow that to happen. Yes, you'll need another data extension. You can specify the primary key fields when you create the target DE. – Adam Spriggs Jul 5 '17 at 13:36
  • In our system, an event can only have one policy, but one policy can be shared across multiple accounts. – jessed Jul 5 '17 at 13:46
  • Also, while I'm not well versed in SQL, I think the last line in your Query above is joining the Member and Event files on AccountId, but in our system, the Event file doesn't have that field, which is why we need to use the PolicyNumber to connect back to the Member file. Do I just need to adjust the last line to be: left join [member info] m on (m.[account id] = p.[account id]) – jessed Jul 5 '17 at 14:09
  • How you define uniqueness in the target data extension will need to accommodate that one-to-many possibility. – Adam Spriggs Jul 5 '17 at 14:10

Not the answer you're looking for? Browse other questions tagged or ask your own question.