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

Simple 1-N table Join Help Needed

Avatar

Avatar
Validate 1
Level 2
scottgaulin
Level 2

Likes

4 likes

Total Posts

11 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile

Avatar
Validate 1
Level 2
scottgaulin
Level 2

Likes

4 likes

Total Posts

11 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
View profile
scottgaulin
Level 2

07-06-2017

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

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Coach
MVP
david_garcia1
MVP

Likes

106 likes

Total Posts

184 posts

Correct Reply

41 solutions
Top badges earned
Coach
Contributor
Shape 1
Give Back 25
Give Back 10
View profile

Avatar
Coach
MVP
david_garcia1
MVP

Likes

106 likes

Total Posts

184 posts

Correct Reply

41 solutions
Top badges earned
Coach
Contributor
Shape 1
Give Back 25
Give Back 10
View profile
david_garcia1
MVP

09-06-2017

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.

Answers (1)

Answers (1)

Avatar

Avatar
Validate 10
MVP
Amit_Kumar
MVP

Likes

329 likes

Total Posts

649 posts

Correct Reply

231 solutions
Top badges earned
Validate 10
Validate 1
Establish
Give Back 50
Give Back 5
View profile

Avatar
Validate 10
MVP
Amit_Kumar
MVP

Likes

329 likes

Total Posts

649 posts

Correct Reply

231 solutions
Top badges earned
Validate 10
Validate 1
Establish
Give Back 50
Give Back 5
View profile
Amit_Kumar
MVP

07-06-2017

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