what is the "like" SQL function on Alteryx for searching and replacing data

[DavidP07]

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.

[alexnajm]

Sounds like a CONTAINS statement might work, but we can give better insight if you have some examples!

[AndrewDMerrill]

Regex_Match will also work. More info would be very helpful to evaluate your specific use case.

[DavidP07]

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.

[Hammad_Rashid]

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:

1. Contains Function:

   • The Contains function checks if a string contains a specified substring.
   • Example: Contains([Field], "substring")

2. ContainsOnly Function:

   • The ContainsOnly function checks if a string contains only the specified substring.
   • Example: ContainsOnly([Field], "substring")

3. FindString Function:

   • The FindString function returns the position of the first occurrence of a substring in a string.
   • Example: FindString([Field], "substring") > 0

4. 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:

// Example: Replace "old_value" with "new_value" if [Field] contains "substring" IF Contains([Field], "substring") THEN "new_value" ELSE [Field] ENDIF

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.

[AndrewDMerrill · Accepted solution]

Here is a sample workflow that accomplishes what you're looking for by sorting weekdays from Monday to Friday and changing all days of the week to "todos los dias".

[DavidP07]

Thanks a lot for your help. Great, I will be working on it by this method.

[DavidP07]

Thank you very much for this solution, I will be working on it, I hope I can get those days organized. :)