Finding the last instance of a character within a text string in Alteryx

Early I was doing some playing around, looking to manipulate file path strings to develop an application that unions all files in the same location and of the same type as a template file (yes I know these macros already exist but I wanted to develop my own to enable me to understand the workings).

The route I went for was that the user browsed to one of the files, and that I would then manipulate this path and essentially replace the filename with the Alteryx wildcard character ‘*’.

This means that I wanted to manipulate a string that looks like this…

C:\Users\Ben Moss\Desktop\filenamehere.filetypehere

into something that looked like this…

C:\Users\Ben Moss\Desktop\*.filetypehere

Given the syntax within filepaths I knew that anything after the last backslash (\) character and the first (and only) full stop represented the filename.

So my challenge was to acknowledge the positions of these characters.


Finding the location of a character is relatively simple, and most of you will probably know that, in Alteryx, the following formula…

findstring([field],”.”)

Will return me the position of the first full stop within the field.

It is possible, using a series of nested findstrings with a combination of the right and length functions that we can find the nth occurence of the character.

Something like

findstring([Field1],”\”)

//position of 1st backslash

+

findstring(right([Field1],length([Field1])-findstring([Field1],”\”)-1),”\”)

//position of 2nd backslash in string trimmed from after first back slash

+1

//add character because the first backslash is taken out of the equation but it still forms part of the string

Would return the 2nd instance for example. However, what if we don’t know the value for N. We don’t know the value of N (filepaths can contain any number of back slashes), but we do know we need the final one, thats for sure.

2017-01-26_18-18-25

That’s when, whilst browsing Alteryx’s string functions, I fell upon the ‘ReverseString()’ function, which does exactly what it says on the tin.

ReverseString(‘Ben’) for instance would return ‘Neb’.

So

ReverseString(C:\Users\Ben Moss\Desktop\filenamehere.filetypehere)

would return

erehepytelif.erehemanelif\potkseD\ssoM neB\sresU\:C

Which whilst looking likely absolute nonsense, this is actually very useful as we can now use the findstring() function to return the position of the 1st backslash in our reverse string, before then taking that away from the length of the string to return it’s actual position.

2017-01-26_18-06-51

Now we have acknowledged our position we just need to combine all of our formulas to develop our final wildcard path.

2017-01-26_18-04-18

2017-01-26_18-08-31

And there you go, that’s how I resolve my string issue.

Ben

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s