Find the next closest date between 2 attributes

hi @ngstoke has a custom transgormer very powerful to calculate the difference and find the next closest dates

https://hub.safe.com/transformers/datedifferencecalculator

Thanks,

Danilo

Hi @ngstoke

is there a chance that Well(n) START_DTE will not be the closest START_DTE after Well(n-1) COMP_DTE? Can it happen to be before the Well(n-1) COMP_DTE? Are you looking for the START_DTE that is after COMP_DTE?

You will definitely find new FME 2017 datetime functionality very helpful. I also think you might need AttributeCreator with conditional values with adjacent features - the AttributeCreator at its best :)

Please share a small data sample here if you have one - it will help us brainstorming.

I have used a spatial approach to solve this sort of issue before

Convert the dates to seconds and use these to create boxes, then find the 2 nearest neighbours, and discard the one that is earlier to find the nearest start date

select
    a.WELL_NUM,
    a.START_DTE,
    a.COMP_DTE,
    b.WELL_NUM as NEXT_WELL_NUM,
    b.START_DTE as NEXT_START_DTE,
    b.COMP_DTE as NEXT_COMP_DTE,
    (strftime('%s', b.START_DTE) - strftime('%s', a.COMP_DTE))/86400 as _diff
from well as a cross join well as b on a.COMP_DTE <= b.START_DTE
inner join (
    select
        a.WELL_NUM,
        min(b.START_DTE) as START_DTE
    from well as a cross join well as b on a.COMP_DTE <= b.START_DTE
    group by a.WELL_NUM
) as c on c.WELL_NUM = a.WELL_NUM and c.START_DTE = b.START_DTE
order by a.WELL_NUM 

<result>{
    let $wells := {
        let $sd := fme:get-list-attribute("_list{}.START_DTE")
        let $cd := fme:get-list-attribute("_list{}.COMP_DTE")
        for $n at $i in fme:get-list-attribute("_list{}.WELL_NUM")
        let $w := <w n="{$n}" sd="{$sd[$i]}" cd="{$cd[$i]}"/>
        order by xs:date($w/@sd) ascending
        return $w
    }
    for $a at $i in $wells
    let $b := fn:subsequence($wells, $i + 1)[xs:date($a/@cd) <= xs:date(@sd)][1]
    where fn:exists($b)
    return
    <well>
        <WELL_NUM>{xs:integer($a/@n)}</WELL_NUM>
        <START_DTE>{xs:date($a/@sd)}</START_DTE>
        <COMP_DTE>{xs:date($a/@cd)}</COMP_DTE>
        <NEXT_WELL_NUM>{xs:integer($b/@n)}</NEXT_WELL_NUM>
        <NEXT_START_DTE>{xs:date($b/@sd)}</NEXT_START_DTE>
        <NEXT_COMP_DTE>{xs:date($b/@cd)}</NEXT_COMP_DTE>
        <_diff>{fn:days-from-duration(xs:date($b/@sd) - xs:date($a/@cd))}</_diff>
    </well>
}</result>