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

Tsurapaneni
Tsurapaneni Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Registered Posts: 41 ✭✭✭✭

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

  • StanG
    StanG Dataiker, Registered Posts: 52 Dataiker
    edited July 2024

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



  • Tsurapaneni
    Tsurapaneni Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Registered Posts: 41 ✭✭✭✭

    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.

Setup Info
    Tags
      Help me…