This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
How do you convert a string with a $ sign into a number?
1. First remove the dollar sign and commas out of the string
2. Convert () to a negative sign "-"
3. ToNumber() function
There are many ways to do this in Alteryx, here are a few:
Formula Tool - ToNumber(Replace(Replace(Replace(Replace([Data], '$', ''), ',' , ''), ')' , ''), '(' , '-'))
Formula Tool - ToNumber(REGEX_Replace(REGEX_Replace([Data], "\$|,|\)", ""), "\(", "-"))
RegEx Tool #1 - Replace \$|,|\) with Replacement Text "blank", then
RegEx Tool #2 - Replace \( with Replacement Text "-"
Select Tool - Change Type to Fixed Decimal
See attached workflow.
The RegEx tool is kind of like the Swiss Army Knife of parsing in Alteryx; there are a whole lot of ways you can use it to do things faster or more effectively, but even if you just use the blade it's still immensely useful. Sometimes that's all you need, but if you do take the time to figure out how to use a few other tools in that knife, you'll start to see that there isn't much you can't do with it .
This article features a workflow that will allow you to search for keywords within a column of text strings. Potential applications for this might be to scan a Twitter feed, customer product feedback or Facebook comments for keywords such as the mention of particular people, descriptive words or subject matters. For this example, the purpose will be to search famous science quotes for a select group of keywords.
In the first section of the workflow, we are performing the following functions:
Reading in the data - The first table contains the text strings to be searched (quotes); the second table contains the key words that we want to search for.
Adding a Record Id in order to join the original text strings back into the data.
Using the Text to Columns tool, we will split the text strings into individual rows for each word within each text string.
Append each of the key words to each of the records/words resulting from the previous step.
In the next section of the workflow, we will:
Use the Multi-Field Formula tool to change all words in both the text strings and keywords to uppercase as the FindString function used downstream is case sensitive.
Use the RegEx tool to remove non-word characters from the text string words (such as periods, commas, etc.) so that we can do a double-check on the FindString by using a length test. This will prevent the FindString function from returning a match of the keyword 'know' to the text string 'knowledge'.
Match words using the Formula tool and the FindString function in conjunction with comparing the length of the keywords to the text string words.
In the final section of the workflow, we will complete the steps of:
Summarizing the counts of each keyword to each Record ID of the text strings.
Joining back in the original text strings.
Sorting the data back to its original state.
Transposing the data in order to perform a cross-tab count of the keywords.
Performing a final summary count of the keywords.
Things to consider:
Your text strings might contain non-word characters not accounted for in the RegEx in this example. Make adjustments to the RegEx as appropriate.
The workflow results can be used to make a Word Cloud
As with almost anything Alteryx, there is more than one way to do things. Try some variations and post anything you think is worthwhile!
This article was put together to resolve a common issue with cleansing your data as well as to show the use of tools and techniques that are not normally used for newer users. The goal of the article is to get newer users into these tools to open their creativity with the tool and hopefully take you to the next level!
In this use case, the data in the attached workflow is messy with capitalized strings all over the place. We want to format the data by removing some of the capitalization, but not all of it.
Note: If we wanted to make every first letter of the word capitalized we can use the Formula Tool and the TitleCase(String) function. This would make BEAR the WEIGHT - Bear The Weight. See the difference?
The tools that we will be using in this exercise is the Record ID, Text to Columns, RegEx, Formula, Tile, and Cross Tab Tools.
The exercise will show you the importance of using the Record ID Tool. The flexibility of the Text to Columns and RegEx Tools, the under-used Tile Tool, the creativity of the Formula Tool, and the not so scary Cross Tab tool when then data is configured properly.
We hope that these exercise and use cases open up your mind and the greatness of Alteryx!
See attached workflow and enjoy!
Between the RegEx , Text To Columns , and XML Parse Tools , the Alteryx data artisan already has an exceptionally robust selection of tools to help parse uniquely delimited data. However, there are still some data sets so entangled in formatting that it’s labor intensive to parse even for them. Enter the Find and Replace Tool , which captures the ability to find your nightmarish parsing workflows and replace them with sweet color by number pictures. Just kidding, it finds bad jokes and replaces them with good ones. Seriously, though, you could do both if you wanted to because this tool has the capability to look up a table of any number of specified targets to find in your data and will replace them with a table of specified sources. With the help of a few quick configuration steps, this tool can simplify some parsing use cases significantly.
If you're like me, you may find RegEx a bit intimidating. While it can be confusing, once you get a handle on it you'll see just how powerful it actually is. Below is a post from the Discussion boards that should make an introduction into RegEx less daunting. The full post is below, and I've attached the sample workflow to this post as well.
A lot of data prep questions I get asked about seem to come back to the same answer - "Use Regex!"
However, RegEx isn't exactly easy to understand for someone who has never come across it before.
This is how I started out, but by dissecting other peoples' uses of RegEx, I started to understand how RegEx works and now I seem to use it in pretty much every workflow I create (slight exaggeration).
So I decided to upload the attached workflow that has 12 use cases for RegEx for you to use, adapt, learn from and improve on. Please download the attached Alteryx workflow and explore what's going on and you'll soon be more than comfortable with RegEx too!
I've also put some useful RegEx links on the original blog for this workflow here: http://www.theinformationlab.co.uk/2016/10/27/regex-practice-using-alteryx/
Feel free to upload your use cases for RegEx on this discussion post, along with examples, and perhaps we can start to create a small repository for everyone - from RegEx newbies to those highly skilled in this complex but extremely useful tool.
The attached workflow is in v10.5. Refer to our RegEx Tool Mastery if you'd like to learn more about the tool and its many uses!
Business Problem: Data preparation is time consuming and inefficient. Analysis requires multiple formats and types of data to be brought together within a streamlined process. Data warehouses are often incompatible silos of information that are both difficult and impractical to integrate with other data sources. Actionable Results:
Quickly and easily parse complex data streams
Integrate data from various disparate sources
Create scalable and repeatable data cleansing workflows
Overview: Effective strategic analysis often involves the integration of several types of disparate data formats. In the past, this has been an arduous task, and could pose a daunting challenge to even the most experienced analysts. Alteryx simplifies data hygiene techniques by providing streamlined functionality as both an ETL and processing tool. With modules that are simple to customize and run, tasks such as data parsing become feasible in a short amount of time. Any type of data cleansing can be easily accomplished using Alteryx, and every workflow created becomes scalable and repeatable. This module demonstrates how virtually any data stream can manipulated and formatted with relative ease. Vertical: Applicable to any vertical Key Tools Used: Text to Columns,Transpose, Multi-Row Formula, Summarize (Group By )
RegEx….it can be tough, but extremely useful when looking for ways of extracting information from a string. Regular Expressions are basically a code you can write to match a specific set of characters (it's a pattern matching syntax). It could be something as simple as finding the three-digit number in the string “Eample123Eample” or something more complicated like using hexadecimal to select a certain range of characters. RegEx is something that can be used in Alteryx via the RegEx tool in the Parse toolset and some of the main uses for RegEx are replacing, matching, and parsing specific characters within a string.