Note, if this blog looks familiar, that's because its been ported from my personal blog site, benjnmoss.wordpress.com.
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.
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.
Now we have acknowledged our position we just need to combine all of our formulas to develop our final wildcard path.
And there you go, that’s how I resolve my string issue.
Now, 2019 Ben has had a bit more exposure to Alteryx than 2017 Ben, and he now knows about the function 'FileGetFileName()', which could have been used in this scenario.
FileGetFileName([FilePathField])
However, that is only useful in filepath scenarios, but this blog has far wider applications than that!
Ben