The Dataiku Frontrunner Awards have just launched to recognize your achievements! SUBMIT YOUR ENTRY

Dataiku formula language for SQL 'IN'?

Solved!
PANKAJ
Level 3
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?

 

0 Kudos
1 Solution
Ignacio_Toledo

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.

View solution in original post

0 Kudos
6 Replies
Ignacio_Toledo

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.

View solution in original post

0 Kudos
PANKAJ
Level 3
Level 3
Author

Thanks, man.

But I found this in the Dataiku documentation before your reply.

Anyway Thanks again @Ignacio_Toledo 

PANKAJ
Level 3
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?

0 Kudos
Ignacio_Toledo

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!

0 Kudos
PANKAJ
Level 3
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?

0 Kudos
Ignacio_Toledo

I can only think about:

not(arrayContains([array...], column))

The negation of array contains. And this can be used with the logical operators... however I think my previous solution, while longer, should also allow the user of the logical operators. But haven't tried. 

Cheers! 

0 Kudos
A banner prompting to get Dataiku DSS
Public