Filter rows of a table based BETWEEN condition of a column

[mars519]

I’m trying to filter a table so that only rows where the column DATE are between two values are returned using the column expression node. In the expression editor, I have written:

if BETWEEN(20240101, column("DATE"), 20240131){
column("$DATE$")}
ELSE {"Null"}

I then want to use the rule based row filter to filter out the nulls.

However I get the error:

Errors in code. Please fix the expression.
No function ‘BETWEEN’ with 3 argument(s) defined. if BETWEEN(20240101,column(“DATE”) ,20240131 ) { ^ at line 1 at column 4

[sforesti]

Hi @mars519,

If your DATE field is coded as a date, I would use the Date&Time-based Row Filter node for this.

Kind regards

[mars519]

Hi,

It is not coded as a date, rather a double.

How do I convert double to date?

Thanks

[sforesti]

How is the double formatted? Would 2024-01-21 appear as 20240121?

[mars519]

Yes, it’s formatted exactly like that.

[sforesti]

You can use this in the Column Expression node: regexReplace(column("DATE"),"([0-9]{4})([0-9]{2})([0-9]{2})","$1-$2-$3"). Make sure you also change the DATE field type to Local Date in the Column Expression “Type” dropdown. You can then filter the DATE field using the Date&Time-based Row Filter node.

[mars519]

Thanks, this worked.