Matching a field using multiple keyword

CM100

Hi Everyone,

Appreciate some help with the following

Am looking to conduct "if contain "String1 or String2", then "String" on the following example field on a formula tool

Objective is the following

- if Campaign column contains "(S)" and "Brand" then "Branded Paid Search"

Elseif

- if Campaign column contains "(S)" only then "Generic Paid Search"

Campaign

(S) Car Insurance_Brand_BMM

(S) Car Insurance_Brand_EM

(S) Car Insurance_Cheap_BMM

(S) Car Insurance_Cheap_EM

Was wondering if Regex is the best method to go about addressing this ?

if it is what will the Regex be ?

Thanks in advance !!

Common Use Cases

Transformation

Data Investigation

Accepted answers

AndrewS

Hi,

Simple Formula with a couple of IFs should handle this.

IF
Contains([Campaign], '(S)') AND Contains([Campaign], 'Brand')THEN "Branded Paid Search"
ELSEIF Contains([Campaign], '(S)') THEN "Generic Paid Search"
ELSE 'NO RESULT' ENDIF

IF.yxmd

lmorrell

Hi @CM100

Solution is attached.

This can be done with an ELSEIF statement containing two CONTAINS() functions, or with a similar logic but specifying the exact pattern via a REGEX_MATCH() function.

In this example, an ELSEIF function would look like

if contains([Campaign], '(S)') AND contains([Campaign], 'Brand') then 'Branded Paid Search'
elseif Contains([Campaign], '(S)') then 'Generic Paid Search'
else [Campaign]
endif

and a REGEX_MATCH() would look like

if REGEX_Match([Campaign], '.*?\(S\).*?Brand.*') then 'Branded Paid Search'
elseif regex_match([Campaign], '^.*?\(S\).*') then 'Generic Paid Search'
else [Campaign]
endif

If you encounter performance issues, then I find that ELSEIF statements are a little quicker.

Hope this helps!

Matching a field using multiple keyword.yxmd

AndrewS

Hi @CM100

To change a contains to a does not contain all you need to do is place a '!' before contains - as in

!Contains([dsCampaign], ...

All comments

AndrewS

Hi,

Simple Formula with a couple of IFs should handle this.

IF
Contains([Campaign], '(S)') AND Contains([Campaign], 'Brand')THEN "Branded Paid Search"
ELSEIF Contains([Campaign], '(S)') THEN "Generic Paid Search"
ELSE 'NO RESULT' ENDIF

IF.yxmd

lmorrell

Hi @CM100

Solution is attached.

This can be done with an ELSEIF statement containing two CONTAINS() functions, or with a similar logic but specifying the exact pattern via a REGEX_MATCH() function.

In this example, an ELSEIF function would look like

if contains([Campaign], '(S)') AND contains([Campaign], 'Brand') then 'Branded Paid Search'
elseif Contains([Campaign], '(S)') then 'Generic Paid Search'
else [Campaign]
endif

and a REGEX_MATCH() would look like

if REGEX_Match([Campaign], '.*?\(S\).*?Brand.*') then 'Branded Paid Search'
elseif regex_match([Campaign], '^.*?\(S\).*') then 'Generic Paid Search'
else [Campaign]
endif