Simple 1-N table Join Help Needed

[scottgaulin]

Hello,

We are trying to create a 1-n join from Recipients to a small offer detail table and are having trouble with the join. Can anyone help? We've engaged our install consultants but they are having issues with it too.

this is a simple join to the recipient table off of Member Key and using the Offer ID as the primary key for this table.

We are able to add data and pull data in a query, but sedning deliveries (mail/email) fails at pulling the recipient. I recieve an error as follows:

The delivery has failed

An error occurred and the process has been stopped. Error while executing the method 'PrepareTargetImpl' of service 'nms:delivery'. The delivery cannot be started because the resources are not available. Please restart the analysis process

It looks like the delivery cannot find the recipients. We are also having an issue deleting data from the table - it wants to deleted the full recipient record and not just the offer detail.

Here's our offer detail table

<element desc="Loan Preapproval Offer Details" label="LPA Offer Details" labelSingular="LPA Offer Detail"

           name="lpaOfferDetails">

    <key name="offerNumber">

      <keyfield xpath="@offerNumber"/>

    </key>

    <!--Offer Attributes-->

    <attribute label="Offer Number" name="offerNumber" type="string"/>

    <attribute label="Member Key" name="memberKey" type="long"/>  (link to recipients)

    <attribute label="Offer ID" name="offerID" type="string"/>  (primary key for this table)

    <attribute label="Date Opened" name="dateOpened" type="date"/>

    <attribute label="Date Closed" name="dateClosed" type="date"/>

    <attribute label="Expiration Date" name="offerExpirationDate" type="date"/>

    <attribute label="Offer Rate" name="offerRate" type="double"/>

    <attribute label="Offer Amount" name="offerAmount" type="long"/>

    <attribute label="Offer Term" name="offerTerm" type="long"/>

<!--Recipient Schema Link-->

    <element advanced="false" externalJoin="true" label="Recipients" name="recipient"

             revCardinality="unbound" revLabel="LPA Offer Detail" target="nms:recipient"

             type="link">

      <join xpath-dst="@memberKey" xpath-src="@memberKey"/>

    </element>

  </element>

</srcSchema>

Thank you in advance

Best,

Scott

[Amit_Kumar]

Hi Scott,

You are not doing it right because in this you have can have more than one offer for recipient so while sending the delivery you can't use the link directly as this will not resolve to a single record for example Recipient R1 can have Offer O1, O2.. On(n can take values from 0 to Infinity).

You can solve this in two ways:

1. Either make it 1:1 mapping(which i believe is not possible given you business scenario)

2. Or Load Offer details as Additional data in query using enumeration link or nested query.

Hope this helps!

Regards,

Amit

[david--garcia · Accepted solution]

Try sending a simple delivery using an enrichment to link the two schemas using reconciliation keys first. (https://db.tt/XK2RBoa4x4 )

1. Query Recipients targets and add complementary memberkey/email and any other member data needed to populate the email
2. Add an enrichment and set the targeting dimension to the schema that holds the offer data
3. Use simple reconciliation setting the source and destination to memberkey
4. Execute and check that data results contain offer/recipient data accordingly by right clicking on the transition line. (display target) https://db.tt/4f8x8OzeYO
5. email template variables either targetData.offers123 or recipient.FirstName depending on targeting dimensions.