what is the "like" SQL function on Alteryx for searching and replacing data
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi, I have this issue, how can I use a formula similar to the one in SQL "like" to find specific data in order to transform it into a different one, thanks.
Solved! Go to Solution.
- Labels:
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Sounds like a CONTAINS statement might work, but we can give better insight if you have some examples!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Regex_Match will also work. More info would be very helpful to evaluate your specific use case.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Look, there are multiple responses for a single answer that I would like to transform into "todos los dias" which means all days in English. "Lunes Martes Miercoles Jueves and Viernes" are the days of the week in Spanish. So, some students in a survey answered all of those responses in disorder, for example, " Martes, Jueves, Miercoles, Viernes, Lunes" which represents the same as "Lunes Martes Miercoles Jueves Viernes" so my ultimate goal is too classy them as "todos los dias" which means all days in English. It sounds a bit weird, but I hope both pictures can help. Also, I have the same problem but for shorter values such as "Martes and Jueves" or "Jueves and Martes" Which technically means the same but they are in a different order so I gotta find a way to organize this, I will appreciate your help. Also, I will attach the DB so you can take a look further.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
In Alteryx, you can achieve similar functionality to the SQL "LIKE" clause using the Contains, ContainsOnly, FindString, or MatchRegex functions, depending on your specific needs. Here's a brief overview of these functions:
Contains Function:
- The Contains function checks if a string contains a specified substring.
- Example: Contains([Field], "substring")
ContainsOnly Function:
- The ContainsOnly function checks if a string contains only the specified substring.
- Example: ContainsOnly([Field], "substring")
FindString Function:
- The FindString function returns the position of the first occurrence of a substring in a string.
- Example: FindString([Field], "substring") > 0
MatchRegex Function:
- The MatchRegex function allows you to use regular expressions for pattern matching.
- Example: MatchRegex([Field], ".*substring.*")
To use these functions for searching and replacing data, you can incorporate them into the Formula tool in Alteryx. Here's a general example:
Replace [Field], "old_value", "new_value", and "substring" with your actual field name, the value you want to replace, the new value, and the substring you're searching for, respectively.
Remember to customize the formula based on your specific use case and data. If you need more complex pattern matching, consider using regular expressions with the MatchRegex function.
Additionally, Alteryx has a graphical interface that allows you to perform similar operations using tools such as the Filter tool or the Find and Replace tool. These tools provide a visual way to filter or replace data based on specific conditions without writing formulas manually.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks a lot for your help. Great, I will be working on it by this method.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you very much for this solution, I will be working on it, I hope I can get those days organized. :)