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

Filter out all caps records

ddye
8 - Asteroid

Hi, 

 

I have data where I am trying to remove the row if a spefic field is in all caps.  I thought I may be able to count lowercase characters, and filter out the zeros, but I am not sure why I am getting an incorrect result from my formula.  Any ideas?  Thank you.

 

Formula: REGEX_CountMatches([Task Name], "[\L]")

Task Name               Count

DEMOLITION              0

D.O.T. Approaches      4

Sidewalks                    0

Transformer Pad         1

 

 

9 REPLIES 9
JohnJPS
15 - Aurora

Can you do [A-Z] instead of [\L]?

ddye
8 - Asteroid

Thanks John,

 

That doesn't seem to specifically count lowercase only characters.  It looks like it is just counting all letters.

 

I am not sure what [\L] is counting either though, but it is pretty close to what I am trying to do.

Federica_FF
11 - Bolide

HI,

 

try with

 

REGEX_CountMatches([Column], "\u", 0)

 

\u means uppercase, 0 is needed if you want to activate the case sensitive.

ddye
8 - Asteroid

Looks like it has something to do with how many seperate words there are in the field.  One word records are getting a count of 0, regardless of how many lowercase characters there are in the word.  

JohnJPS
15 - Aurora

Confirmed here... wow... that is weird.

 

Anyway, this seems to work:

Contains(UPPERCASE([f1]),[f1],0)

... where [f1] is the field in question.

 

A-ha, and @Federica_FF spotted the case sensitivity flag on the REGEX functions too!  Good eye. Smiley Very Happy

 

ddye
8 - Asteroid

Thanks!  That works, and is getting me what I need.  Really appreciate it.

 

I am a bit confused though as to why I would get a 0 result on CARPENTRY, which is what it is giving me.  

BenMoss
ACE Emeritus
ACE Emeritus

Here is a slightly different method to that outlined in the above answers (probably seen as less efficient also) but it works none the less and essentially expands from an answer given yesterday.

You create a line for each of the individual characters within the string and use to chartoint() formula to return the numeric code for that character. From this we can flag characters that are capitals (capital letters return a value between 60 and 95, as outlined in this table http://www.asciitable.com/).

We can then create a count of the uppercase flag.

count caps.png

ddye
8 - Asteroid

That is really cool.  I am about to start working with some pretty nasty data, so I may try to incorporate this in order to filter out junk unicode i know it is going to have in it. 

 

Thanks!

alfredcsy
8 - Asteroid

alfredcsy_0-1670369347475.png

 

Labels