This question is rather arbitrary and should just create a bare minimum usecase that is simple to understand.

Sample Raw Data (arbitrary) lets call it SubscriberState

| SubscriberKey |   State     |
|        123456 | Not Started |
|       1234567 | Started     |
|        124567 | Exited      |
|        123789 | Errored     |

I want to use a LookUp Function like LookUpRows and count the rows where state is either "not Started" or "Started".

How is it possible to write ONE Amspscript statement that does this, without making two LookUp Calls?

Rowcount(LookUpRows("SubscriberState","State","Not Started OR Started", "SubscriberKey", _subscriberKey))

or to make it even more simple:

Rowcount(LookUpRows("SubscriberState","State","Not Started OR Started")

Possible workaround two calls (do not wanna use it):

Add(Rowcount(LookUpRows("SubscriberState","State","Not Started", "SubscriberKey", _subscriberKey)),Rowcount(LookUpRows("SubscriberState","State","Started", "SubscriberKey", _subscriberKey)))

What i would like to know aswell: Can i somehow use a like parameter LIKE '% Started' or include conditions like 'OR'? Or to rephrase it: How can i achieve the maximum functionality out of LookUp Calls [to get as close to the functionality of SQL itself]?


LookupRows and Lookup simply is not set up to do this, the condition has to be an exact match; Hence you cannot use "LIKE" or "OR" in those functions.

https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-programmatic-content.meta/mc-programmatic-content/lookuprows.htm -

"Returns a rowset from a data extension where the field matches the specified value."

You could use advanced SSJS filters to some extent for this, but that likely does not answer your question as SSJS is not AMPScript.

If you want to make ONE AMPscript call, you'll have to preprocess your data, e.g. filter down a DE which only has all the relevant records, give every single record the same value in the same field ("include == 1") and run the AMPScript lookup on that. That probably also doesn't answer your question, because you'd basically use SQL for the needed SQL functionality, and you wouldn't solve this with AMPscript code in any way.

In sum: this is a limitation of AMPScript - to my knowledge, you'll probably have to find other ways as outlined.


When speaking on a higher level -

I'd even speculate that AMPScript is designed to have these limits. Such limitations are actually necessary to keep AMPScript performant, and are meant to nudge you to process more complex logic outside of sending context with SQL and/or SSJS.

| improve this answer | |
  • Hi Jonas. Thank you for the answer in first place. I share your opinion on a higher level but in some cases (for example mine) it would be a lot faster doing the State = 'Started' OR State = 'Not Started'call [not best for performance] or the call State LIKE '%Started' [really good performance] instead of two calls ;) – Johannes Schapdick Jul 13 at 10:26
  • I see what you mean. From a product management perspective, you'll have the cut the functionality at some point, even at the cost of some legit functionality, as there are workarounds for those elsewhere in the product. So limiting the conditions available sounds like an obvious approach - after all, how much sense will "retrieve more possible records" make with a row limit of 2500. Not trying to defend any (speculative) decisioning here, that's just how I make sense of it :) – Jonas Lamberty Jul 13 at 10:41

I agree with Jonas's comments, Lookup AMPScript functions aren't intended to be used for this and probably intentionally so. This answer is for completeness.

Although Lookup functions can't implement complex filtering logic as they match based on simple equality logic, you can implement more sophisticated filtering logic using API AMScript functions in your Landing Pages. Here's an example of a retrieve against your SubscriberState Data Extension:

  SET @rr = CreateObject("RetrieveRequest")
  AddObjectArrayItem(@rr, "Properties", "SubscriberKey")

  /* Define SimpleFilterParts */
  SET @sfp1 = CreateObject("SimpleFilterPart")
  AddObjectArrayItem(@sfp1,"Value"," Started")

  SET @sfp2 = CreateObject("SimpleFilterPart")

  /* Define ComplexFilterPart */
  Set @cf = CreateObject("ComplexFilterPart")

  /* set the filter of request */
  SET @subs = InvokeRetrieve(@rr, @status)

  SET @count = RowCount(@subs)
Records: %%=v(@count)=%%

By the sound of things, your use case only requires a single "existsInString" SimpleFilterPart, rather than the more elaborate ComplexFilterPart using "OR" above, although I suspect you'll get better performance with "OR". But you still have the issue of being limited to 2500 records as there's no capability to page through MoreDataAvailable and the crazily slow performance.

| improve this answer | |
  • This is indeed a great consideration when using cloudpages. My example (intentionally not provided) was using these calls for exclusionsscripts (not usable use case ;D). But this answer may offer a really solid ground when you want to analyze data on processing cloudpages. Thank you for the post. – Johannes Schapdick Jul 13 at 14:51
  • You can trick your exclusion script to execute contentblocks. so it might actually work, if you are not afraid of going down that rather "hacky"-feeling rabbithole. – Jonas Lamberty Jul 13 at 14:59

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.