AMP script LookUpOrderedRows with deduplication

Question

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!

Answer 1

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.