This week, I ran into a data engineering problem where I needed to extract a JV number from a string of numbers. The JV number can have 3,4, or 6 digits. For any number less than 6 digits, the system adds leading zeros. At first, I thought I needed to dig into some Regex, but there was a much simpler answer to remove leading zeros in Alteryx. Read on to add another tool to your Alteryx toolbox.
Now, I learned the Spotfire expression language before I learned Alteryx syntax. Spotfire used the Trim function to remove whitespace, so when I learned Alteryx, I looked for the same expression. Alteryx has a Trim function and it does in fact remove whitespace. In fact, the default configuration is to remove whitespace with Trim. Because I thought I knew what Trim did, I never dug any further. However, that’s not all you can do with Trim.
The Trim Function
The Trim function removes the character(s) specified from the ends of the string. Y is optional, and the function defaults to trimming white space. Click on the link for additional examples.
Trim(String, y) -- basic syntax
Trim([JV Number], "0") -- my expressionThere are also options to TrimLeft and TrimRight, which is what I used in my use case. TrimLeft removes characters from the beginning of the string, and TrimRight removes characters from the end of the string.
Remove Leading Zeros
TrimLeft did the job perfectly because zeros were always added to the start of my JV Number.
And that is how you can quickly and easily remove leading zeros in Alteryx!
Pingback: Update SharePoint Drop Down Lists Using Alteryx Tools » The Analytics Corner