Count cell if contains a string, but not count if same string is PART of a string

[csh8428]

I have a field called Roles. It can have any/all/none combination of the following values: "Manager", "Associate", "Survey Recipient", "OE Survey Recipient"

I'm trying to count the # of employees who are Survey Recipients(that specific string). As long as the role has "Survey Recipient". If it ONLY has "OE Survey Recipient" it should NOT be counted. For the purposes of this workflow you can ignore the "meaning" of Survey Recipient and just think of it as a string.

I've been trying to achieve this using various iterations of if contains and !contains, but because "OE Survey Recipient" ALSO "contains" the string "Survey Recipient" it is counting these when it shouldn't.

Here's an example:

Name	Role	Should it be Counted	Why?
Bob	Manager, Survey Recipient	Y	Is a Survey Recipient
Julie	OE Survey Recipient	N	Is NOT a "Survey Recipient"
Jon	Survey Recipient, OE Survey Recipient	Y	Is a Survey Recipient
Will	Associate	N	Not a survey recipient

I'm betting RegEx is the way to go, but I'm relatively new to Alteyrx and that's a bit intimidating!

Thanks for any help!

Craig

[kelsey_kincaid]

Hi @csh8428 

I think you can get what you need out of an IF statement rather than jumping to RegEx. IF Statements have an order of operations, so you can check for OE Survey Recipients first and set those to N, then check for Survey Recipients, then anything else. Give this a try and let me know if it works for you!

IF [Role]="OE Survey Recipient" THEN "N" ELSEIF
Contains([Role],"Survey Recipient") THEN "Y" ELSE "N" ENDIF

[csh8428]

Thanks for the help!

using "=" won't work in this case because the field may contain multiple roles in any combination or order, so "contains" is required; hence my issue of "OE Survey Recipient" also containing "Survey Recipient" causing my problem.

If an EE is both an "OE Survey Recipient" and "Survey Recipient" they should be counted, but that if statement doesn't count this EE in this case because they met the first condition in the statement.

[kelsey_kincaid]

@csh8428 You could create an intermediary field where you replace anywhere that says OE Survey Recipient in Roles with a different word like "Exclude" and then build your IF statement off of that instead. Example attached.

[csh8428 · Accepted solution]

This was close, but wouldn't have worked because it would have excluded OE Survey Recipients. I didn't want to "exclude" them if they were also Survey Recipients.

This did lead me to the right solution though.

I used the replace function to replace "OE Survey Recipients" with a blank in an intermediary field and then used that intermediary field for the If contains function. 

[estherb47]

Hi @csh8428 

Wonderful that you solved your own question!! I was also working on this and would like to propose a bit more flexible solution. I love your thinking of using RegEx to make this more dynamic (e.g., what if the string you didn't want to match were ABC Survey Recipient in addition to OE Survey Recipient)

So I did a few things. The fact that a single line could contain what you want to count and what you don't want to count is a big challenge. Splitting into rows so that there is only one piece of text to analyze per rows helps.

My function solution:

IF REGEX_Match([Role], ".*[A-Z]+\sSurvey\sRecipient") THEN Null()     \\This takes any string of Survey Recipient that begins with at least one other capital letter
ELSEIF Contains([Role], "survey recipient") THEN "Y" \\This matches on the string you want
ELSE Null()

ENDIF

I used Null() for things that don't match, so that when the Summarize tool rebuilds the rows, you don't have extra stuff to clean up.

Another formula tool replaces nulls with "N". Funny thing I learned. When concatenating Null values, they are counted as Empty, not Null.

Please let me know if this works for you.

Cheers!

Esther