I have a question about whether what I want to accomplish with ampscript is even feasible in the Email product.
At the moment I am looking up a rows of data from an accounts data extension using the below code:
set @accountRows = LookupRows("accounts","userid",_subscriberkey)
This can return multiple rows of data since one subscriber might have multiple accounts. I want to then pick the account that was last update or had the latest activity by that user. The problem is that there is no column in accounts for last updated. Instead last updated is stored in a separate data extension called accountdetails. If it weren't for that, I would have used lookuporderedrows() instead.
My question is, is there a way using ampscript, to join accounts to accountdetails and then pick the accountid with the latest activity? If I were using SQL, I would probably write something like the following:
select au.accountid, ad.last_updated from accounts au left join accountdetails ad on ad.accountid = au.accountid where au.userid = _subscriberkey order by ad.last_updated desc limit 1;
I guess the difficult part is this needs to happen at the time of email send.