First, Explanation:

So, I have a Data Extension and every time a visitor on our website visits a product several times, that product ID is sent to a Data Extension.

My Data Extension has these fields:

SubscriberKey, EmailAddress, SHA256, Country Code, ListImageType, ID

By using Lookup I'm able to find out if each visitor has shown interest in a certain product and show them that product or related products.

But if a visitor have shown interest in several products over time, I only want to show them the latest product.. or products related to that product.

So, Question:

I want use Lookup to return a product ID by searching for a visitor ID. But if the visitor appears more than once in my Data Extension, I only want to return the latest entry. How can I accomplish this - can I perhaps alter my code to search from 'the bottom of' my Data Extension?

My Lookup:

SET @ID = Lookup('ProductInterestTriggerDE','ID','SHA256',v(SHA256))
  • Is there not a date field in your data extension? You'd need that. – Adam Spriggs Jan 22 '18 at 13:59

First, I'd suggest adding a date field to your data extension -- one that's nullable and defaulted to today's date. Once that's done, you can leverage the LookupOrderedRows() function for rows added from that point forward:

var @rows, @row, @rowCount, @numRowsToReturn, @SHA256, @i

set @SHA256 = AttributeValue("SHA256")
set @numRowsToReturn = 1 
set @rows = LookupOrderedRows("ProductInterestTriggerDE", @numRowsToReturn, "CreatedDate desc", "SHA256", @SHA256)
set @rowCount = rowcount(@rows)

if @rowCount > 0 then

    set @row = row(@rows,1) 
    set @ID = field(@row,"ID")
    set @ListImageType = field(@row,"ListImageType")
    set @CountryCode = field(@row,"Country Code")

    output(concat("<br>ID: ", @ID))
    output(concat("<br>ListImageType: ", @ListImageType))
    output(concat("<br>CountryCode: ", @CountryCode))


output(concat("No rows found"))


| improve this answer | |
  • Thanks Adam, helpful as always :) I'll give this solution a try, and get back to you. – user40290 Jan 26 '18 at 11:29
  • 1
    What you've suggested above was a super solution. Easy and quick to implement and working like a charm. Super advise! Thanks – user40290 Jan 30 '18 at 8:39

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.