In between operator in Dataiku

pnaik1

How do we implement following SQL Where clause using Dataiku Prepare recipe (Filter rows or cell with formula):

(table1.col1) Between "0*" And "9*" Or (table1.col1) Between "A*" And "Z*")

Can't find anything related to Between operator in dataiku.

Thanks!!!

Sarina

Hi @pnaik1
,

From your example, my understanding is that you are looking for a match of:

col1 starts with 0-9 or col1 starts with A-Z. Is that understanding correct?

If so, I would suggest using a "Extract with regular expression" step and selecting "Create a special found column" with the regular expression:

(^[A-Z].*)|(^[0-9].*)

Here's an example of the step:

This will provide you with an output column with the values true/false, where the true values meet the condition: starts with A-Z or starts with 0-9. You can then filter on your found column to filter for "true" matches.  

To answer your original question regarding a formula step, you can use the < and > operators within a formula to represent "in between". So if I was looking for the column 'col1' and wanted all values in between 0000 and 9999 for example, you could have a formula step that is: col1 > 0000 && col1 <= 10000. For the use case you outlined here, it seems like a regular expression would be the easiest way to evaluate though.

Let me know if you have any questions about this approach.

Thank you,
Sarina