Assuming you are sending to DE_X
, product_code
will be a substitution string.
There are a couple ways to do this - most of it depends on your data, and how long a full lookup of rows might take.
Lookup()
If all you are after is the name and price, you could do two quick lookups.
%%[
/* Assuming first row only - use for loop for additional rows */
@price = lookup('DE_Y', 'product_price' ,'product_code', product_code)
@name = lookup('DE_Y', 'product_name' ,'product_code', product_code)
]%%
Lookup[Ordered]Rows(), Lookup[Ordered]RowsCS()
If there are multiple rows to match on product code, use lookuprows()
, or lookuporderedrows()
. Using lookuporderedrows()
will order them by a specific field (ie price, or date). Note these are not case specific. Adding the cs
at the end of the function name will ensure that cases are matched.
This method may be a little better, as the row set is cached, so the functions after the initial look up are more performant.
%%[
@rowset = LookupRows('DE_Y','product_code', product_code)
/* Assuming first row only - use for loop for additional rows */
@price = field(row(@rowset,1), 'product_price')
@name = field(row(@rowset,1), 'product_name')
]%%
Queries and Performance
If you are pulling from several data extensions, try to get your data as flat as possible prior to sending. A lookup or two to one data extension won't hinder your send speeds much, but the more complexity you add to your data, the longer a send can take.
NOTE: The impact to each email being sent is in microseconds - so if your sends are in the thousands, you wouldn't see much impact. If your sends are in the hundred thousands, things get much slower due to the scale of the send.