Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

csh8428
11 - Bolide

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:

NameRoleShould it be CountedWhy?
BobManager, Survey RecipientYIs a Survey Recipient
JulieOE Survey RecipientNIs NOT a "Survey Recipient"
JonSurvey Recipient, OE Survey RecipientYIs a Survey Recipient
WillAssociateNNot 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

5 REPLIES 5
kelsey_kincaid
12 - Quasar

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
11 - Bolide

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
12 - Quasar

@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.PNG

csh8428
11 - Bolide

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
15 - Aurora
15 - Aurora

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.

 

image.pngimage.png

 

Please let me know if this works for you.

 

Cheers!

Esther

Labels