How to check if column value is in between of two other column values

Tsurapaneni
Level 3
How to check if column value is in between of two other column values

Hi Team,

I am having a use case where I have to create a new column based on 3 columns X, Y and Z. If a value of X is in between the range of columns Y and Z then the new column has the value X else the complete row is deleted. I am using a postgre in - database sql engine for computation. Can you please help me in writing a formula which best suits this use case ? ( I couldn't use in between in the formula as it is showing me an error, may be my usage is wrong in this case).

Note: All the columns are date columns

Please do the needful !

0 Kudos
1 Reply
MehdiH
Dataiker

Hi @Tsurapaneni 

If all columns are correctly parsed as Date, then you can

  • use the following formula in the formula step of your prepare recipe, to create a new column satisfying the condition:
if(X <= Z && X >= Y, X, "")
  • Then add another step to remove all rows where the newly created column has an empty value

If the columns are not parsed as Date (their meaning is Date (unparsed) or something else), then you should first use the "Parse date" step to do so.

Let me know if this helped

Cheers,

Mehdi