Discover the winners & finalists of the 2022 Dataiku Frontrunner Awards!

# Dataiku formula language for SQL 'IN'?

Solved!
Level 3
###### Dataiku formula language for SQL 'IN'?

Suppose I want to run a SQL query in which query is like " WHERE City IN ('A', 'B', 'C') AND ID IN (1,2,3) OR NAME='John' ".

I'm trying this in prepare recipe --> filter rows with formula

what's the analogous formula for "IN" in Dataiku?

1 Solution

Hello @PANKAJ,

With DSS 9.0.0, this formula should do the work:

( arrayContains(['A', 'B', 'FAIL'], City) && arrayContains([1, 2, 3], ID) ) || NAME == 'John'

I think it will work also with previos DSS versions, but I thought it was important to include the version just in case.

Hope this helps.

6 Replies

Hello @PANKAJ,

With DSS 9.0.0, this formula should do the work:

( arrayContains(['A', 'B', 'FAIL'], City) && arrayContains([1, 2, 3], ID) ) || NAME == 'John'

I think it will work also with previos DSS versions, but I thought it was important to include the version just in case.

Hope this helps.

Level 3
Author

Thanks, man.

Anyway Thanks again @Ignacio_Toledo

Level 3
Author

@Ignacio_Toledo What if we want to that for "NOT IN A LIST"?

I mean how can we use 'arrayContains' for NOT IN?

If I understand correctly, I'd try:

arrayContains(['A', 'B', 'FAIL'], City) == "False"

So, if the column City doesn't contains A, B or Fail arrayContains will return False, and with the comparison operator == "False" we get the result expected for NOT IN (is True that City is not in A, B or Fail)

Hope this works!

Level 3
Author

@Ignacio_Toledo No, I want to write that along with && ,|| Operators.

So is there something like arraynotContains or Not before the list of values?