Searching the column values in a list of words - prep recipe

Tsurapaneni
Level 3
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

 

0 Kudos
2 Replies
StanG
Dataiker

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))



0 Kudos
Tsurapaneni
Level 3
Author

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. 

0 Kudos