Check for date that falls between two "dates"

[MRB]

Greetings,

I want to test whether a date field falls between two dates I choose, but I cannot get e simple test on one date to work, what am I doing wrong?

I created a new column based on another date column (resolved)

if(value > "10/01/2018".toDate(), true,false)   

and if(resolved.value() >=  "10/01/2018".toDate(), true,false)   everything is false.

What is the proper syntax, and what syntax would you recommend for comparing between two dates, I'm perusing the GREL docs, but I'm struggling.

Thank you,

Mark

Results Preview

7.

2019-10-07T10:10:00Z

false

[Owen Stephens]

Hi Mark,

You need the "diff" function:

diff(date1,date2,"units") will give a difference between date1 and date2 in the specified units ("units" can be "seconds", "minutes", "hours", "days", "months","years"...)

If date1>date2 the number will be positive, if date1<date2 then negative.

So I'd use:

if(diff(value,"10/01/2018".toDate(),"days")>0, true,false)

Best wishes

Owen

[Thad Guidry]

Mark,

What place did you look at first in our docs on the wiki?

Did you do a Wiki search from Github's black bar?

(don't choose "Code" from the left menu after you type your search text, but instead "Wiki")

When I typed "date" into the search bar... the first Wiki page that comes up in search is the GREL Date functions page which mentions diff().

When working with GREL... its always best to go to the GREL Reference and then navigate around, or just use the WIki search feature from Github's black bar.

Thad

https://www.linkedin.com/in/thadguidry/

--
You received this message because you are subscribed to the Google Groups "OpenRefine" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openrefine+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/openrefine/96216a94-789d-4008-ac33-44b13f81d61b%40googlegroups.com.

[MRB]

[MRB]

oops ... sorry.

Thanks you both for your replies.

I did look at the date doc - but it's not obvious to a new user that the diff function was the best way to do date checking ... 

I'm actually going to have to break the date apart and determine the fiscal year the date falls in, ie  for us it's 10/1 to 9/30 for a fyxx. 

I had one last question, is there a direct way to facet a boolean field, so I get a quick count of true vs false? or do I need to use an if and create a text field to facet?

Thanks again,

Mark

[Owen Stephens]

Hi Mark,

With OpenRefine releases except 3.1 you can just do a text facet on the column and boolean values will convert to their string equivalents of "true" and "false" in the facet.

If you are using OpenRefine 3.1 you will need to do a custom text facet with the GREL:

value.toString()

and that will do the same thing

Owen

[Owen Stephens]

For breaking the date apart the 

datePart()

function may be helpful. So for example you could create a financial year column using something like

if(value.datePart("month")<10,"Financial Year "+(value.datePart("year")-1).toString()+"-"+value.datePart("year").toString(),"Financial Year "+value.datePart("year").toString()+"-"+(value.datePart("year")+1).toString())

I think!

Owen

On Tuesday, October 8, 2019 at 7:08:23 PM UTC+1, MRB wrote:

[Mark Bustin]

Thank you , I’m using 3.1, and the workaround is great! Thanks.

--
You received this message because you are subscribed to a topic in the Google Groups "OpenRefine" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/openrefine/uNMocJVDl7k/unsubscribe.
To unsubscribe from this group and all its topics, send an email to openrefine+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/openrefine/7a1e8b9e-8f30-4591-a7d1-08f527a306c0%40googlegroups.com.

[MRB]

Thanks Owen!