Announcing the winners & finalists of the Dataiku Frontrunner Awards 2021! Read their inspiring stories

In between operator in Dataiku

pnaik1
Level 3
In between operator in Dataiku

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!!!

 

0 Kudos
1 Reply
SarinaS
Dataiker
Dataiker

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: 

Screen Shot 2021-06-28 at 11.37.17 AM.png

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

0 Kudos
A banner prompting to get Dataiku DSS