AMPScript language country and date variables

Question

I'm working on creating a personalized display with multiple delivery dates based on date, country and language for every user.

I have a data extension with the dates, message for each country and the default language.

The problem is that some customers have a different language then the default language and I would like to display the message based on the Customer preferred language and the dates based on the country.

For the example in the screenshot, if I take the content based on es, then it will also pickup the country name that is implemented in the message column for es language. I can change to take up the content based on language and dates based on country but message might have another country name which might not be same as the users country because the language is not the default language

That is why I have scripted the email like if the users country and language combination from recipients DE is found in another DE then only populate the email

Is there a way to achieve the display to show the message in the language that the customer has set with the country dates that the customer has set?

If you have any questions please let me know

    %%[
SET @customerID = AttributeValue('CustomerID')

SET @user_rows = LookupRows('recipients','CustomerID',@customerID)
IF RowCount(@user_rows) > 0 THEN
SET @user_row = ROW(@user_rows,1)
SET @preferredcountryid = FIELD(@user_row,'preferredcountryid')
SET @language = FIELD(@user_row,'language')

IF EMPTY(@language) THEN
SET @language = 'en'
ENDIF

IF NOT EMPTY(@preferredcountryid) THEN

SET @date_rowsl = LookupRows('Xmas-Delivery-Dates','Language',@language,'preferredcountryid',@preferredcountryid)
IF RowCount(@date_rowsl) > 0 THEN
SET @date_rowl = ROW(@date_rowsl,1)
SET @date1_message = FIELD(@date_rowl,'LastBikeMessage')
SET @date2_message = FIELD(@date_rowl,'LastFreeMessage')
SET @date3_message = FIELD(@date_rowl,'LastPaidMessage')
SET @date4_message = FIELD(@date_rowl,'NextDayMessage')
SET @defaultdeliverymessage = FIELD(@date_rowl,'DeliveryOptionMessage')
SET @view = FIELD(@date_rowl,'VIEWDELIVERYDATES')
SET @gvhead = FIELD(@date_rowl,'giftvoucherHEADLINE')
SET @gvtext = FIELD(@date_rowl,'giftvoucherTEXT')
SET @gvshop = FIELD(@date_rowl,'giftvoucherSHOPNOW')
ELSE
RaiseError('Language and country combination not found','true')
ENDIF

ELSE

SET @date_rowsl = LookupRows('Xmas-Delivery-Dates','Language',@language)
IF RowCount(@date_rowsl) > 0 THEN
SET @date_rowl = ROW(@date_rowsl,1)
SET @date1_message = FIELD(@date_rowl,'LastBikeMessage')
SET @date2_message = FIELD(@date_rowl,'LastFreeMessage')
SET @date3_message = FIELD(@date_rowl,'LastPaidMessage')
SET @date4_message = FIELD(@date_rowl,'NextDayMessage')
SET @defaultdeliverymessage = FIELD(@date_rowl,'DeliveryOptionMessage')
SET @view = FIELD(@date_rowl,'VIEWDELIVERYDATES')
SET @gvhead = FIELD(@date_rowl,'giftvoucherHEADLINE')
SET @gvtext = FIELD(@date_rowl,'giftvoucherTEXT')
SET @gvshop = FIELD(@date_rowl,'giftvoucherSHOPNOW')
ELSE
RaiseError('Language not found','true')
ENDIF

ENDIF

IF EMPTY(@preferredcountryid) THEN
SET @mts = @defaultdeliverymessage
ELSE
SET @date_rows = LookupRows('Xmas-Delivery-Dates','preferredcountryid',@preferredcountryid)
IF RowCount(@date_rows) > 0 THEN
SET @date_row = ROW(@date_rows,1)
SET @country_name = FIELD(@date_row,'Country')
SET @date1 = FIELD(@date_row,'LastBike')
SET @date1 = FORMATDATE(@date1,'MM/DD/YYYY')
SET @date2 = FIELD(@date_row,'LastFree')
SET @date2 = FORMATDATE(@date2,'MM/DD/YYYY')
SET @date3 = FIELD(@date_row,'LastPaid')
SET @date3 = FORMATDATE(@date3,'MM/DD/YYYY')
SET @date4 = FIELD(@date_row,'NextDay')
SET @date4 = FORMATDATE(@date4,'MM/DD/YYYY')

IF EMPTY(@date1) AND EMPTY(@date2) AND EMPTY(@date3) AND EMPTY(@date4) THEN
SET @j = 1
ENDIF

SET @today = NOW()
SET @today = FORMATDATE(@today,'MM/DD/YYYY')

IF NOT EMPTY(@date1) THEN
SET @dtsc1 = DateDiff(@today,@date1,'D')
ELSE
SET @dtsc1 = 10000
ENDIF

IF NOT EMPTY(@date2) THEN
SET @dtsc2 = DateDiff(@today,@date2,'D')
ELSE
SET @dtsc2 = 10000
ENDIF

IF NOT EMPTY(@date3) THEN
SET @dtsc3 = DateDiff(@today,@date3,'D')
ELSE
SET @dtsc3 = 10000
ENDIF

IF NOT EMPTY(@date4) THEN
SET @dtsc4 = DateDiff(@today,@date4,'D')
ELSE
SET @dtsc4 = 10000
ENDIF

IF @dtsc1 < @dtsc2 AND @dtsc1 < @dtsc3 AND @dtsc1 < @dtsc4 AND @dtsc1 != 10000 AND @dtsc1 >= 0 THEN
SET @i = 1
ELSEIF @dtsc2 < @dtsc1 AND @dtsc2 < @dtsc3 AND @dtsc2 < @dtsc4 AND @dtsc2 != 10000 AND @dtsc2 >= 0 THEN
SET @i = 2
ELSEIF @dtsc3 < @dtsc1 AND @dtsc3 < @dtsc2 AND @dtsc3 < @dtsc4 AND @dtsc3 != 10000 AND @dtsc3 >= 0 THEN
SET @i = 3
ELSEIF @dtsc4 < @dtsc1 AND @dtsc4 < @dtsc2 AND @dtsc4 < @dtsc3 AND @dtsc4 != 10000 AND @dtsc4 >= 0 THEN
SET @i = 4
ELSE
SET @i = 5
ENDIF

IF @i == 1 THEN
SET @mts = @date1_message
SET @dts = FORMATDATE(@date1,'DD/MM/YYYY')
ELSEIF @i == 2 THEN
SET @mts = @date2_message
SET @dts = FORMATDATE(@date2,'DD/MM/YYYY')
ELSEIF @i == 3 THEN
SET @mts = @date3_message
SET @dts = FORMATDATE(@date3,'DD/MM/YYYY')
ELSEIF @i == 4 THEN
SET @mts = @date4_message
SET @dts = FORMATDATE(@date4,'DD/MM/YYYY')
ELSEIF @j == 1 THEN
SET @mts = @defaultdeliverymessage
ELSEIF @i == 5 THEN
SET @mts = @gvhead
SET @dts = @gvtext
SET @view = @gvshop
ELSE
ENDIF

ELSE
RaiseError('Country ID not found','true')
ENDIF
ENDIF


ELSE
RaiseError('Subscriber Not Found','true')
ENDIF
]%%

<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body>
<!-- White divider --><table class="fullwidth" style="width:600px;" cellspacing="0" cellpadding="0" border="0" align="center"><tr><td style="height:10px; background-color:#ffffff;" height="10"></td></tr></table>

<!-- Express Delivery START -->

<!--<table class="fullwidth" style="width:600px;" cellspacing="0" cellpadding="0" border="0" align="center"><tr><td style="height:6px; background-color:#ffffff;" height="6"></td></tr></table>

  <table width="100%" cellspacing="0" cellpadding="0" border="0">
  <tr> 
   <td valign="top" align="center">
   <table width="600" cellspacing="0" cellpadding="0" border="0">
     <tr>
      <td valign="top" align="center">
      <table style="background-color:#ffffff;" width="100%" cellspacing="0" cellpadding="0" border="0" bgcolor="#ffffff">
        <tr>
         <td style="font-family:'Roboto Condensed', Arial, Sans-Serif; font-size: 16px; color: #3c5d6c; font-weight: normal; line-height: 16px; padding-top: 5px; padding-right: 30px; padding-left: 30px;" valign="top" align="center">
           preferredcountryid: %%=TreatAsContent(@preferredcountryid)=%%<br>
           language: %%=TreatAsContent(@language)=%%<br>
           date1_message: %%=TreatAsContent(@date1_message)=%%<br>
           date2_message: %%=TreatAsContent(@date2_message)=%%<br>
           date3_message: %%=TreatAsContent(@date3_message)=%%<br>
           date4_message: %%=TreatAsContent(@date4_message)=%%<br>
           date1: %%=TreatAsContent(@date1)=%%<br>
           date2: %%=TreatAsContent(@date2)=%%<br>
           date3: %%=TreatAsContent(@date3)=%%<br>
           date4: %%=TreatAsContent(@date4)=%%<br>
           today: %%=TreatAsContent(@today)=%%<br>
           dtsc1: %%=TreatAsContent(@dtsc1)=%%<br>
           dtsc2: %%=TreatAsContent(@dtsc2)=%%<br>
           dtsc3: %%=TreatAsContent(@dtsc3)=%%<br>
           dtsc4: %%=TreatAsContent(@dtsc4)=%%<br>
           i value: %%=TreatAsContent(@i)=%%<br>
           j avlue: %%=TreatAsContent(@j)=%%<br>


          </td>
        </tr>
      </table>
      </td>
     </tr>
   </table>
   </td>
  </tr>
</table>

  <br><br><br>-->
<table width="100%" cellspacing="0" cellpadding="0" border="0">
  <tr> 
   <td valign="top" align="center">
   <table width="600" cellspacing="0" cellpadding="0" border="0">
     <tr>
      <td valign="top" align="center">
      <table style="background-color:#ffffff;" width="100%" cellspacing="0" cellpadding="0" border="0" bgcolor="#ffffff">
        <tr>
         <td style="font-family:'Roboto Condensed', Arial, Sans-Serif; font-size: 16px; color: #3c5d6c; font-weight: normal; line-height: 16px; padding-top: 5px; padding-right: 30px; padding-left: 30px;" valign="top" align="center">%%=TreatAsContent(@mts)=%% </td>
        </tr>
      </table>
      </td>
     </tr>
   </table>
   </td>
  </tr>
</table>
%%[ IF @j == 1 THEN]%%
%%[ ELSE ]%%
<table width="100%" cellspacing="0" cellpadding="0" border="0">
  <tr>
   <td valign="top" align="center">
   <table class="wrap100pc" width="600" cellspacing="0" cellpadding="0" border="0">
     <tr>
      <td valign="top" align="center">
      <table style="background-color:#ffffff;" width="100%" cellspacing="0" cellpadding="0" border="0" bgcolor="#ffffff">
        <tr>
         <td style="font-family:'Roboto Condensed', Arial, Sans-Serif; font-size: 16px; color: #3c5d6c; font-weight: bold; line-height: 30px; padding-right: 30px; padding-left: 30px;" valign="top" align="center">%%=TreatAsContent(@dts)=%%</td>
        </tr>
      </table>
      </td>
     </tr>
   </table>
   </td>
  </tr>
</table>
%%[ ENDIF ]%%
<table width="100%" cellspacing="0" cellpadding="0" border="0">
  <tr>
   <td valign="top" align="center">
   <table class="wrap100pc" width="600" cellspacing="0" cellpadding="0" border="0">
     <tr>
      <td valign="top" align="center">
      <table style="background-color:#ffffff;" width="100%" cellspacing="0" cellpadding="0" border="0" bgcolor="#ffffff">
        <tr>
         <td style="font-family:'Roboto Condensed', Arial, Sans-Serif; font-size: 11px; color: #91b9ca; font-weight: normal; line-height: 22px; padding-right: 30px; padding-left: 30px;" valign="top" align="center"><a target="_blank" href="https://www.google.com" style="outline:none; text-decoration:underline; font-family:'Roboto Condensed', Arial, Sans-Serif; color:#91b9ca;" border="0">%%=TreatAsContent(@view)=%%</a></td>
        </tr>
      </table>
      </td>
     </tr>
   </table>
   </td>
  </tr>
</table>


<table style="width:600px" cellspacing="0" cellpadding="0" border="0" align="center"><tbody><tr><td style="height:10px;background-color:#ffffff" height="10"></td></tr></tbody></table></body>
<code>%%[ if 0 == 1 then ]%% 
%%Member_Busname%%
%%Member_Addr%%
%%Member_City%%
%%Member_State%%
%%Member_PostalCode%%
%%Member_Country%%
</code>&lt;a href=&quot;%%profile_center_url%%&quot; alias=&quot;Update Profile&quot;&gt;Update Profile&lt;/a&gt;&lt;div style=&quot;text-align: center;&quot;&gt;
<code>%%[ endif ]%%</code>
<custom name="opencounter" type="tracking"/>
</html>

Answer 1

I found it a bit hard to understand the question, but if I got it now, then the key question is this:

a)Is there a way to achieve the display to show the message in the language that the customer has set

b)with the country dates that the customer has set?

So if I understand correctly, you need to treat language and country independently UNLESS the combination is found...

So basically:

1) check the country the user gives (OK)

2)check the language the user gives (OK)

3)check the language that you have in your DE next to the user's country. (NEW)

If 2 and 3 match, good. Lookup by user country and user language.

If they don't match - fallback: lookup the content by user country and the language you have in the DE next to it (see 3)

I added indentation and comments, please see the bit of code I changed below. Code is not tested, but I hope it gets the idea across.

Hope this helps, let me know in case I misunderstood something.

 %%[
SET @customerID = AttributeValue('CustomerID')

SET @user_rows = LookupRows('recipients','CustomerID',@customerID)

IF RowCount(@user_rows) > 0 THEN
  SET @user_row = ROW(@user_rows,1)
  SET @preferredcountryid = FIELD(@user_row,'preferredcountryid')

/* find the users language */
  SET @language = FIELD(@user_row,'language')

/* find the language in the Data Extension to the users country */

/*updated code*/
  SET @languageFoundForCountryROWSET = LookupRows('Xmas-Delivery-Dates','Language',@language,'preferredcountryid',@preferredcountryid)
  SET @languageFoundForCountryROW = ROW((@languageFoundForCountryROWSET,1)
  SET @languageFoundForCountry = FIELD(@languageFoundForCountryROW,'language')
/* end update */


  /* set default language */
  IF EMPTY(@language) THEN
    SET @language = 'en'
  ENDIF

  If @language == @languageFoundForCountry THEN
    /* the language the user has given matches the language next to the user's given country in the DE, so we can use it in a lookup */
    SET @date_rowsl = LookupRows('Xmas-Delivery-Dates','Language',@language,'preferredcountryid',@preferredcountryid)
  ELSE
    /* the language the user has given + the country he has given are a combination that we don't have in our DE. So we just lookup the texts by what language we have in the DE for the user's country: @languageFoundForCountry  */

    SET @date_rowsl = LookupRows('Xmas-Delivery-Dates','Language',@languageFoundForCountry,'preferredcountryid',@preferredcountryid)
  ENDIF
  IF RowCount(@date_rowsl) > 0 THEN
    SET @date_rowl = ROW(@date_rowsl,1)

    SET @date1_message = FIELD(@date_rowl,'LastBikeMessage')
    SET @date2_message = FIELD(@date_rowl,'LastFreeMessage')
    SET @date3_message = FIELD(@date_rowl,'LastPaidMessage')
    SET @date4_message = FIELD(@date_rowl,'NextDayMessage')
    SET @defaultdeliverymessage = FIELD(@date_rowl,'DeliveryOptionMessage')
    SET @view = FIELD(@date_rowl,'VIEWDELIVERYDATES')
    SET @gvhead = FIELD(@date_rowl,'giftvoucherHEADLINE')
    SET @gvtext = FIELD(@date_rowl,'giftvoucherTEXT')
    SET @gvshop = FIELD(@date_rowl,'giftvoucherSHOPNOW')
  ELSE
    RaiseError('Language not found','true')
  ENDIF

/*....rest of code */

ELSE
  RaiseError('Subscriber Not Found','true')
ENDIF
]%%