community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Count of Leading Spaces in a field

Meteor

Hi i would like to check the number of leading spaces to determine the hierarchy , any idea how to get the leading spaces please?

 

ValueLeading Spaces
   ABC3
      A1B1C16
      A2B2C26
         A21B21C219
         A22B22C229
   DEF3
      D1E1F16
         D11E11F119
Alteryx Certified Partner

Hi @vinay4444,

 

In the formula tool you can use REGEX_CountMatches([Value], "\s") to count the whitespace

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@vinay4444,

 

If there can be no space other than in front of the characters, then @LordNeilLord's solution works.  If spaces may occur elsewhere, I constructed a counting mechanism of:

Length(replace([field1],TrimLeft([Field1]),''))

Inside of this nested formula we do the following:

  1. Remove leading spaces
  2. Remove whatever follows the leading spaces from the field
  3. Count the remaining characters (leading spaces)

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Pulsar

Another option: Length([Value])-Length(TrimLeft([Value]))

Alteryx Certified Partner
Alteryx Certified Partner

@danrh,

 

I agree that your formula works too.  But did you know that it outperforms my formula?  Testing on 10,000,000 records the average run-time of 8.5 seconds was reduced to 7.7 seconds.  In comparison to a regular expression variation of the formula, both are significantly faster by almost 3x.  The regular expression is the tortoise in that race.

 

But don't get too excited about beating me.  I found this expression to be 6.8 seconds and so far the fastest of them all:

 

STRSPN([Field1]," ")

STRSPN(x, y)

Returns the length of the initial segment of the string [x] consisting of characters in the string [y]

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Pulsar

@MarqueeCrew - huh!  I hadn't used STRSPN before.  Thanks for the tip!

Labels