Alteryx Designer

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

Most Effective method to group records by letter?

Highlighted
ACE Emeritus
ACE Emeritus

Hi,

I have a field with letters A-Z in it.  I want to split my data into four "buckets"

 

A-F
G-L
M-R

S-Z

 

I recognize that I can call out every single option in a SWITCH statement, or write a REGEX_MATCH for each of these, but is there a smarter way to handle a problem like this?

The particular function that I can't seem to reliably create is some sort of "Position in Alphabet".  Is this something that exists which I just haven't found?

Highlighted
Alteryx Partner

Here's one way - 

 

You could use the formula tool and the formula (with the example column "letter")

 

CharToInt([Letter])-64

 

which give you the position.

 

You could then use the Tile tool to set up the groupings.

 

Highlighted
ACE Emeritus
ACE Emeritus

Ah - I think what I was missing is the need to normalize the data.

I tried that and was getting inconsistent results, not thinking of the fact that A/a would naturally have different integer values.

 

Thanks!

Highlighted
Alteryx Partner

Glad to help - you can use the Uppercase() function to normalize to uppercase.

 

Cheers,

Bob

 

Labels