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!