0

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.

1

AMPScript does not provide a way to join data when doing a lookup. A query activity could be used to denormalize the data prior to sending then reference that denormalized data extension with your AMPScript lookup instead of the separate accounts/accountdetails data extensions. Depending on the size of the data extensions, this may not be a very performant option.

  • Thanks Mac, just to make sure I understand you correctly. A potential workaround is to run a query that joins the two data extensions. The output of that query would then populate a separate data extension. Finally, I would then write my ampscript look ups against the new extension? – Jerry Jun 16 '15 at 22:31
  • Yup, that is correct. – Mac Jun 17 '15 at 11:32

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.