How to use a IF function containing a certain word?

GSung
Level 3
How to use a IF function containing a certain word?

Hi , I would like to create a new column of data (i.e. column c)  whereby if col A  does not contain the word "number", then col C = Col B*10 and if not , col C = Col B

So for example, the first row in col A contains the word number , so col C = col B(i.e. 10) ; while the second row does not contain the word "number", so Col C = col B * 10 (20* 10= 200)

 

Col ACol BCol C (would like to generate this col)
Number of XX1010
Amount of YY20200
0 Kudos
5 Replies
AgatheG
Dataiker

Hello GSung,

 

You can create such a column C in a Prepare recipe, by adding a Formula step.

In your case, the formula would be:


if(contains(toLowercase(A), "number"), B, B*10)


I used toLowercase to ensure that the if is case insensitive, i.e. for instance Number and number within the A column would yield the same result for C.

You can learn more about the Formula language in the documentation: https://doc.dataiku.com/dss/latest/formula/index.html

 

Hope this helps ๐Ÿ˜Š


Agathe

GSung
Level 3
Author

Thanks Agathe

and how can I expand the formula if I want the condition to contain multiple words --> either contain the word "number" or "a." ?

 

 

0 Kudos
AgatheG
Dataiker

You might want to use an or operator here (refer to the Formula documentation: https://doc.dataiku.com/dss/latest/formula/index.html#boolean-functions).

This would be something like the following:


if(contains(toLowercase(A), "number") || contains(toLowercase(A), "a."), B, B*10)

 

chinazoc
Level 1

@AgatheG Is there documentation for this? How about if i have multiple conditions like more than 3. E.g where a row A contains '10', make column C = 'new value' and where row A contains 20, make column C = 'another value'.

 

This should be like how in sql with the CASE WHEN

0 Kudos
AgatheG
Dataiker

Hi chinazoc,

You might want to have a look at this page: https://doc.dataiku.com/dss/latest/preparation/processors/create-if-then-else.html

It should solve your case, if I understand correctly

(by the way, prefer to open a new post instead of replying to a closed & old one ๐Ÿ™‚ )

 

 Hope that helps,

 

Agathe

0 Kudos