3

I'm writing an AMP script code to have a top 3 of products based on pageviews (=value1) inside an email. I've started with the AMP script funtion LookUpOrderedRows and this gave me the desired outcome. Next step would be to 'deduplicate' based on product id. Products can exist in multiple times in my lookup data extension, but I only want each product to exist at max 1 time in the top 3. So I've created the code below:

%%[
var @rows, @row, @rowCount, @numRowsToReturn, @product, @i

set @campaignid = AttributeValue("campaign_id")
set @numRowsToReturn = 3
set @rows = LookupOrderedRows("Campaign_Interests", @numRowsToReturn, "value1 desc", "campaign_id", @campaignid, "type", "productpageviews")
set @prevProductId = ""
set @rowCount = rowcount(@rows)


for @i = 1 to @rowCount do

    var @product, @pageview, @productid
    set @row = row(@rows, @i) /* get row based on counter */
    set @productname = field(@row,"description")
    set @pageview = field(@row,"value1")
    set @productid = field(@row, "id")
    
 if empty(@prevProductId) or @productid != @prevProductId then
 set @prevProductId = @productid

]%%

    <br> <b> Place %%=v(@i)=%% :</b> %%=v(@productname)=%%, # pageviews: %%=v(@pageview)=%%, %%=v(@productid)=%%

%%[endif]%%
%%[next @i ]%% 

This works as designed but I have one more issue. As the requirement is to show the top 3, I took 3 for the @numRowsToReturn. But when a product id exists e.g. 3 times in the data extension (with same amount of pageviews), the code will now dedup to showing that product 1 time in the top 3. Although, as it are 3 rows, it will only show place 1 and there are no place 2 or 3 (for other products) as there are already 3 in the background.

When I would set @numRowsToReturn to 6 for example. I get a place 1, place 4, place 5, place 6. This could work in some cases but the outcome should always be a top 3.

Is there a way to get the final 3 ranks/places, taking the dedup into account?

Many, many thanks!

1 Answer 1

2

Here you go. This should answer your requirements. What I changed:

  • I've set the second parameter of LookupOrderedRows to 0 to return all matching rows
  • Added a variable called @place
  • Added a condition in your if statement to check if @place has reached 2
  • Incremented the value of @place each time a product is printed
%%[ 
    var @rows, @row, @rowCount, @numRowsToReturn, @product, @i

    set @rows = LookupOrderedRows("Campaign_Interests",  0, "value1 desc", "campaign_id", @campaignid, "type", "productpageviews")
    set @prevProductId = ""
    set @rowCount = rowcount(@rows)
    set @place = 0

    for @i = 1 to @rowCount do

    var @product, @pageview, @productid
    set @row = row(@rows, @i) /* get row based on counter */
    set @productname = field(@row,"description")
    set @pageview = field(@row,"value1")
    set @productid = field(@row, "id")

    if (empty(@prevProductId) or @productid != @prevProductId) and @place < 3 then
      set @prevProductId = @productid
      set @place = Add(@place, 1)
    ]%%

    <br> <b> Place %%=v(@place)=%% :</b> %%=v(@productname)=%%, # pageviews: %%=v(@pageview)=%%, %%=v(@productid)=%%

         %%[endif]%%
    %%[next @i ]%% 

PS: I'm having a hard time formatting this code here in the answer and I don't know why.

3
  • 1
    Thank you so much! The @place variable was the last piece of my puzzle! Many thanks :)
    – JasBas9100
    Commented Jun 14, 2023 at 8:49
  • 1
    I might make @prevProductID into a delimited string and then compare with 'IndexOf' instead just in case you get Product 1 and Product 3 as the same, but since Product 2 was inbetween them, it would still push that product up. Commented Jun 14, 2023 at 14:21
  • Kudos for Rachid and Gortonington, I would combine 2 of your ideas for the answer
    – Duc Le
    Commented Jun 15, 2023 at 10:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .