[SalesForce] Using Lookup to find latest entry in Data Extension

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: 

VAR @ID
SET @ID = Lookup('ProductInterestTriggerDE','ID','SHA256',v(SHA256))

Best Answer

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))

else

output(concat("No rows found"))

endif 

]%%
Related Topic