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 of Leading Spaces in a field

vinay4444
7 - 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
5 REPLIES 5
LordNeilLord
15 - Aurora

Hi @vinay4444,

 

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

MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
danrh
13 - Pulsar

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

MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
danrh
13 - Pulsar

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

Labels