0

I have a send DE and a lookup DE, both have in common the email address. In the lookup DE, the same email address will be listed multiple times if the subscriber has multiple products.

Send DE has the fields: Subscriber_key | Email

Lookup DE has the fields: Email | Product | NumberOfPoints

How can I get all the products and numberofpoints for each email address with AMPscript? I want to be able to assign variables for each product and numberofpoints someone has. E.g. JamesSmith@gmail.com has Visa with 999 points and Mastercard with 12345 points. I need to be able to wrap each of these values in variables so I can compare them later.

I know FOR will do it but I just cannot figure it out.

Sample Code:

%%[

SET @Email = AttributeValue("Email") 
SET @ProductDetails = LookUpOrderedRows("LookUpDE", 0, "Product ASC", "Email", @Email) 
SET @CountPD = RowCount(@ProductDetails)

IF @CountPD > 0 THEN 

  FOR @i = 1 to @CountPD DO 

    SET @Row = Row(@ProductDetails, @i)
    SET @Product = Field(@Row, "Product") 
    SET @NumberOfPoints = Field(@Row, "NumberOfPoints") 

  NEXT @i

ENDIF

]%%

%%=v(@Email)=%%<br>
%%=v(@Product)=%%<br>
%%=v(@NumberOfPoints)=%%<br>

Thanks.

2

As per this situation I would try using LookUpOrdered Rows function as you can get upto 2000 rows in any order set to a specific field. You can also limit the number of rows you want to output by changing the second parameter in the LookUpOrderedRows function.

Note: Setting value of 0 defaults to 2000 rows.

Sample AMPscript:

%%[
SET @Email = AttributeValue("Email")
SET @ProductDetails = LookUpOrderedRows("LookUpDE", 0, "Product ASC", "Email", @Email)
SET @CountPD = RowCount(@ProductDetails)

IF @CountPD > 0 THEN
    FOR @i = 1 to @CountPD DO
        SET @Row = Row(@ProductDetails, @i)
        SET @Product = Field(@Row, "Product")
        SET @NumberOfPoints = Field(@Row, "NumberOfPoints")

    /* YOUR HTML TABLE OR OUTPUT TEXT BELOW */
]%%

   <table width="650" border="0" cellspacing="0" cellpadding="0" style="width:100%; max-width:650px; padding:10px; display:block; border:1px dashed red;">
        <tr>
            <td>
                %%[IF @Product == "productX" AND @NumberOfPoints >= 2000 THEN]%%
                    /* DO SOMETHING */ 
                %%[ENDIF]%%
            </td>
        </tr>
    </table>
%%[
    NEXT @i
ELSE
    SET @message = "There is no product details"
ENDIF
]%%
  • Thanks Brad. I saw something similar before but the problem is, while this spits out the values correctly, I need these values for later use. Here is where I'm at but it returns only one set of values - if an email appear 3 times for example I won't have all value but one 2. – Snowalker Jan 8 '18 at 2:49
  • code%%[ SET @Email = AttributeValue("Email") SET @ProductDetails = LookUpOrderedRows("LookUpDE", 0, "Product ASC", "Email", @Email) SET @CountPD = RowCount(@ProductDetails) IF @CountPD > 0 THEN FOR @i = 1 to @CountPD DO SET @Row = Row(@ProductDetails, @i) SET @Product = Field(@Row, "Product") SET @NumberOfPoints = Field(@Row, "NumberOfPoints") NEXT @i ENDIF ]%% %%=v(@Email)=%%<br> %%=v(@Product)=%%<br> %%=v(@NumberOfPoints)=%%<br> code – Snowalker Jan 8 '18 at 2:49
  • Sorry for the formatting... the documentation says to add 2 spaces at the end where I want to break but it doesn't do anything... I want to be able to do something like this: code%%[IF @Product == "productX" AND @NumberOfPoints >= "2000" THEN]%% do this %%[ENDIF]%%code – Snowalker Jan 8 '18 at 2:55
  • no worries @Snowalker. I have noticed in your code you have set the output variables like %%=v(@ Email)=%%<br> %%=v(@ Product)=%%<br> %%=v(@ NumberOfPoints)=%%<br> after the NEXT @ i. If you look at my code the output should be set before the NEXT @ i statement which will output while running through each loop. thanks – Brad Sapkota Jan 8 '18 at 3:34
  • I know but I need to be able to use @ Product and @ NumberOfPoints in the body of the email for comparisons like IF @ Product == "Visa" AND @ NumberOfPoints <= 2000 THEN do this, ELSE do that. How can I do that? – Snowalker Jan 8 '18 at 12:03
1

The best way I can think to pull these values (without knowing the rowcount or the potential products ahead of time) is to pull them from the DE and place them into a string 'rowset'.

%%[

SET @Email = AttributeValue("Email")
SET @ProductDetails = LookUpOrderedRows("LookUpDE", 0, "Product ASC", "Email", @Email)
SET @CountPD = RowCount(@ProductDetails)

IF @CountPD > 0 THEN 

  FOR @i = 1 to @CountPD DO

    SET @Row = Row(@ProductDetails, @i)
    SET @Product = Field(@Row, "Product")
    SET @NumberOfPoints = Field(@Row, "NumberOfPoints")

    IF @i == 1 THEN

      SET @ProductRowSetStr = @Product
      SET @NumberOfPointsRowSetStr = @NumberOfPoints

    ELSE

      SET @ProductRowSetStr = CONCAT(@ProductRowSetStr. "|", @Product)
      SET @NumberOfPointsRowSetStr = CONCAT(@NumberOfPointsRowSetStr. "|", @NumberOfPoints)

    ENDIF

  NEXT @i

ENDIF

]%%

After this you now have @ProductRowSetStr and @NumberofPointsRowSetStr to utilize the BuildRowSetFromString() AMPScript function on.

example:

SET @ProducRowSet = BuildRowSetFromString(@ProductRowSetStr, "|")

This will then allow you grab the values at your leisure via the Row() and Field() options.

You can then create logic around this (including utilizing IndexOf() on the string to find if a certain value or product exists) to match or personalize based on the content.

e.g.

SET @AmEx = IndexOf(@ProductRowSetStr, "AmericanExpress")
SET @Visa = IndexOf(@ProductRowSetStr, "Visa")

IF @AmEx > 0 AND @Visa > 0 THEN

 **Your Action Here **

ELSE

  **Your Else Action Here **

ENDIF

Depending on the complexity of your need, it may require further FOR loops or conditional statements.

  • 1
    Thanks @Gortonington I appreciate you jumping onto this. I applied what Brad proposed and it worked. Thanks so much both :) – Snowalker Jan 9 '18 at 14:03
  • Glad you got it to work and glad to help! – Gortonington Jan 9 '18 at 14:25

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.