In between operator in Dataiku

Options
pnaik1
pnaik1 Registered Posts: 23 ✭✭✭✭
edited July 16 in Using 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!!!

Answers

  • Sarina
    Sarina Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer Posts: 315 Dataiker
    Options

    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

Setup Info
    Tags
      Help me…