Searching the column values in a list of words - prep recipe
Hi Team,
I have a use case where I have a column named 'text' in dataset A and in the text column I have words and I want few of those to be in lowercase and few to be in the same format of the original one.
In the below example the A column is same and in the text column I wrote a logical condition where " if((Text == 'Mouse')|| (Text == 'Bear'),Text, toLowercase(Text)) " As My dataset has many words I don't want to keep adding the conditions with multiple logical operators like '||' for every condition so, is there any way I can actually put the list of words separated by a comma with a single keyword like "in" or "search" etc.. I don't want to use contains as it doesn't catch the exact match of the word.
NOTE : Please let me know the formulas which can be used in the prepare recipes only - engine using is in sql database engine.
A. Text ====> Output A. Text
1. Mouse 1. Mouse
2. Mice 2. mice
3. Bear 3. Bear
Answers
-
Hi,
You may want to use the arrayContains function: https://doc.dataiku.com/dss/latest/formula/index.html#array-functions
In your example, you could do something like:if(arrayContains(["Mouse", "Bear"], text), text, toLowercase(text))
-
Hi,
Thank you for the solution provided, but I am getting an error saying that "Not supported settings for 'CreateColumnWithGREL' processor: Cannot use SQL engine: the formula is not fully translatable to SQL" as I am using the "In database SQL" engine can you please provide me any SQL code in replacement to this.