2

I'm back for more learning!

Thanks to @adamspriggs blog for all the different examples and resources. However, I'm not fully sure, if I'm understanding how to use 2 different DEs properly? I have attempted to write some codes here. To make thing a little be more complicated one of the DE is sendable and the other DE has 12 products with 3 columns (product number, product name, rank). I'm trying to create a loop for those 12 items that I have ranked 1-12. And I'm trying to show only 4 products in the email based on those 12 items.

    %%[
 var @rowCount, @row, @i, @lookup
 var @memid
 set @memid= AttributeValue("EmailAddress")

 if not empty(@memid) then 
 set @lookup = LookupOrderedRows("DataExtensionNameSendable1", "EmailAdress" @memid)
 set @rowCount = rowcount(@lookup)
endif 
 for @i = 1 to @rowCount do

      set @row = rows(@lookup,@i)
      set @memid = Field(row, "EmailAddress")

   then
     if not empty(@ProductNumber) then
     var @lookup2,@rowCount, @row, @i, @lookup
     var @Pimage, @Pname, @Purl, @ProductNumber
     set @ProductNumber= AttributeValue("ProductNumber")
    endif

 set @lookup2 = LookupOrderedRows("DataExtensionName2", 12, "rank asc", "ProductNumber" @ProductNumber) 
 set @rowCount = rowcount(@lookup2) > = 1 then

 for @i = 1 to @rowCount(@lookup2) do 
      set @row = rows(@lookup2, @i)  
   IF @i == 1 THEN
      set @Pimage = Field(@row, "ProductImageURL")
      set @Pname = Field(@row, "ProductName")
      set @Purl = Field(@row, "ProductURL")
   ELSEIF @i == 2 THEN
      set @Pimage = Field(@row, "ProductImageURL")
      set @Pname = Field(@row, "ProductName")
      set @Purl = Field(@row, "ProductURL")
   ELSEIF @i == 3 THEN
      set @Pimage = Field(@row, "ProductImageURL")
      set @Pname = Field(@row, "ProductName")
      set @Purl = Field(@row, "ProductURL")
   ELSEIF @i == 4 THEN
      set @Pimage = Field(@row, "ProductImageURL")
      set @Pname = Field(@row, "ProductName")
      set @Purl = Field(@row, "ProductURL")
   ELSEIF @i == 5 THEN
      set @Pimage = Field(@row, "ProductImageURL")
      set @Pname = Field(@row, "ProductName")
      set @Purl = Field(@row, "ProductURL")
   ELSEIF @i == 6 THEN
      set @Pimage = Field(@row, "ProductImageURL")
      set @Pname = Field(@row, "ProductName")
      set @Purl = Field(@row, "ProductURL")
   ELSEIF @i == 7 THEN
      set @Pimage = Field(@row, "ProductImageURL")
      set @Pname = Field(@row, "ProductName")
      set @Purl = Field(@row, "ProductURL")
   ELSEIF @i == 8 THEN
      set @Pimage = Field(@row, "ProductImageURL")
      set @Pname = Field(@row, "ProductName")
      set @Purl = Field(@row, "ProductURL")
   ELSEIF @i == 9 THEN
      set @Pimage = Field(@row, "ProductImageURL")
      set @Pname = Field(@row, "ProductName")
      set @Purl = Field(@row, "ProductURL")
   ELSEIF @i == 10 THEN
      set @Pimage = Field(@row, "ProductImageURL")
      set @Pname = Field(@row, "ProductName")
      set @Purl = Field(@row, "ProductURL") 
   ELSEIF @i == 11 THEN
      set @Pimage = Field(@row, "ProductImageURL")
      set @Pname = Field(@row, "ProductName")
      set @Purl = Field(@row, "ProductURL") 
   ELSEIF @i == 12 THEN
      set @Pimage = Field(@row, "ProductImageURL")
      set @Pname = Field(@row, "ProductName")
      set @Purl = Field(@row, "ProductURL")
   ENDIF

]%%

%%[ if not empty(@banner) or not empty(@promocode) or not empty (@ahref) then ]%%

<table cellpadding="0" cellspacing="0" width="100%" style="margin: 0 auto; text-align: center;">
   <tr>

 %%[ if not empty(@Pname) then ]%%

      <td>%%=v(@Pname)</td>

       %%[ endif ]%%

       %%[ if not empty(@Pimage) and not empty (@PruductURL) then ]%%

       <td><img src="%%=v(@Pimage)=%%"><a href="%%=v(@ProductURL)=%%"></a></td>

      %%[ endif ]%%

   </tr>
    <tr>

 %%[ if not empty(@Pname) then ]%%

      <td>%%=v(@Pname)</td>

       %%[ endif ]%%

       %%[ if not empty(@Pimage) and not empty (@PruductURL) then ]%%

       <td><img src="%%=v(@Pimage)=%%"><a href="%%=v(@ProductURL)=%%"></a></td>

      %%[ endif ]%%

   </tr>
    <tr>

 %%[ if not empty(@Pname) then ]%%

      <td>%%=v(@Pname)</td>

       %%[ endif ]%%

       %%[ if not empty(@Pimage) and not empty (@PruductURL) then ]%%

       <td><img src="%%=v(@Pimage)=%%"><a href="%%=v(@ProductURL)=%%"></a></td>

      %%[ endif ]%%

   </tr>
    <tr>

 %%[ if not empty(@Pname) then ]%%

      <td>%%=v(@Pname)</td>

       %%[ endif ]%%

       %%[ if not empty(@Pimage) and not empty (@PruductURL) then ]%%

       <td><img src="%%=v(@Pimage)=%%"><a href="%%=v(@ProductURL)=%%"></a></td>

      %%[ endif ]%%

   </tr>
</table>
%%[ endif ]%%

%%[ else ]%%

<br>row not found

%%[ endif ]%%
0

Based on the research I find:

  • you can't select two DE and write an AMPscript on it. You need to use a Query Activity to nest your second DE. It will create a new set of rows from your primary DE. After you will write an AMPscript to look up multiple column values from multiple ordered rows.

Process:

  • Used Email Studio or Journey Builder to create your Query Activity(depend on your campaign)
  • Write an AMPscript Lookup() or LookupOrderRows()

Please correct me if I have missed a step or my answer is wrong.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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