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

Substring() function having issues

jmelcher
6 - Meteoroid
I have previous program experience and I officially have no clue what is going on.  

I am paring something that follows this pattern "STREETNAME|CITY, STATEABRREVIATION ZIP" (Currently my address parsing tool doesn't exist in the .dll on the server).  So I successfully parsed the Street name, abbreviation and zip.  The problem is with the City. 

It only works on some and I have no idea why. I found the position of "|" and the position of "," . And took the substring between those. It works for some.  But for other it will list the first position as 28 (I made it it's own field for troubleshooting) the second is 35.  But the substring given is 15 characters long.

Any ideas will be helpful (the inconsistency is what is tripping me up)
2 REPLIES 2
kane_glendenning
10 - Fireball
Hi Jacob,

I'm not sure if this is the issue here, but I have previously mucked up the substring value by putting the start and end values into the formula when the substring formula is Substring(String,start,length), so in the case you listed the formula should be Substring(String,28,8) to get the characters between 28-35 inclusive.

That being said, In this case, I would use a Tet to Columns tool on the Pipe, then one on the comma, then Trim(Field) and finally another T2C on the Space.

Hope this helps you troubleshoot.

Kane
benjamin_carley
6 - Meteoroid
Hello!

Instead of doing a few seperate substring formulas to parse out your information, you could try using the regex parsing tool. This way you could split your data out into columns without having to use the Substring.

For the regex expression I've assumed that each entry is on a seperate line and each line follows this strict format:
"(start of line) STREETNAME|CITY, STATEABRREVIATION ZIP (end of line)"

To parse this you could use the regex expression:
 "^(.+)|(.+), (.+) (.+)$" or even "(.+)|(.+), (.+) (.+)"
The ^ key looks for the start of the line, (.+) marks any number of characters as desired text, i.e. streetname or city, | denotes the pipe symbol but it's a special character so a backslash is needed and the $ symbol denotes the end of the line.


Hope this helps!
-Ben
Labels