Shift Date in Column Expressions Node

[stevelp]

I’m using the column expressions node to fill in missing data from other fields.

Here’s the logic that I’m using so far.

if(isMissing(column("Date1")))
    {if(isMissing(column("Date2")))
        {column("Date3") - 15}
     else
        {column("Date2")}}
else
    {column("Date1")}

Basically, if Date1 is missing, use Date2. If Date2 is missing, use 15 days before Date3.

Every part of this works just fine except for when I’m trying to subtract 15 days from Date3. I’m getting this error:

An error occurred during the execution of the script: Cannot convert ‘Double’ to ‘Local Date’.

I’ve tried using a few of the date functions, like:

{column("RecDate") - periodOfDays(15)}

But that gives me the same error. Any tips for how to modify dates in the Column Expressions node?

[stevelp]

I don’t know if it’s easier or more complex, but the calculation I need to do is a combination of values from a couple other columns. Basically, I’m trying to get the midpoint between two date fields. This is what I tried:

{column(“Date3”) + ((column(“Date4”) - column(“Date3”))/2)}

I got the following error:

Error occurred during the conversion of the result ‘2020-02-25NaN’ to ‘Local Date’.

[elsamuel]

LocalDate objects cannot be combined with Double values (the 15 that you’re trying to subtract) using simple arithmetic operations.

In the same vein, the periodOf functions return a value of type Period which has a very specific format. These also cannot simply be added to or subtracted from LocalDate values (such as the Dates you’re trying to manipulate).

I think you should use the plusTemporal function in line 3. It takes in a LocalDate value and a Duration / Period value and outputs a LocalDate value:

if(isMissing(column("Date1")))
    {if(isMissing(column("Date2")))
       {plusTemporal(column("Date3"),periodOfDays(-15))}
     else
        {column("Date2")}}
else
    {column("Date1")}

[stevelp]

This worked perfectly. Thank you!