Dataiku formula language for SQL 'IN'?

PANKAJ

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?

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.

PANKAJ

Thanks, man.

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

Anyway Thanks again @Ignacio_Toledo

PANKAJ

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

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

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!

PANKAJ

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

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

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!