Solved

Find the next closest date between 2 attributes

ngstoke
Badge

I have a list of attributes that have starting and completion dates listed for different teams.

After sorting the starting date column I'm trying to use the first attributes completion date to find the next attribute in the list that has the next closest starting date, then calculate the difference in days.

Below is a sample of my dataset.

So far I've been unable to find a way to automate this process in my workflow.

Any suggestions are much appreciated.

icon

Best answer by takashi 19 August 2017, 08:45

View original
1 Attachment

10 replies

danilo_fme
Rank
Userlevel 4
Badge +30

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

ngstoke
Badge

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 @danilo_inovacao I've tried using this transformer before but I can't get it to find the next start date that is nearest to its completion date for the calculation.

 

 

For the list of completion dates I need it to determine what well has the next closest start date.

 

So the next well with the closest start date after Well 1's Completion date is Well 2 with a 10/26 start date.

 

 

I can get the transformer to calculate the different for each row but I'm having trouble getting it to look for the closest start date in relation to all the other wells in the list, not between its own start and completion dates.

 

 

I hope that makes better sence.

 

 

L
Badge

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.

takashi
Rank
Userlevel 2
Badge +17

Hi @ngstoke, if I understand your intention correctly, this workflow could work.

Note: I created this workflow with FME 20170. In the latest FME release version 2017.1, the DateFormatter has been upgraded to the DateTimeConverter.

Is this your desired result?

takashi
Rank
Userlevel 2
Badge +17

Hi @ngstoke, if I understand your intention correctly, this workflow could work.

Note: I created this workflow with FME 20170. In the latest FME release version 2017.1, the DateFormatter has been upgraded to the DateTimeConverter.

Is this your desired result?

The DateTimeCalculator in FME 2017.0+ can also be used effectively. If the date format in the source features was '%Y%m%d' originally, the DateFormatter (DateTimeConverter in FME 2017.1) would not be essential.

 

 

ngstoke
Badge

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.

Hi @LenaAtSafe I am looking for a way to identify the next closest START_DTE that occurs after each wells COMP_DTE. The ideal output would be a sorted list that identifies from the oldest date to the most recent the order in which the wells were drilled (STE_DATE to COMP_DTE).

 

 

There will instances in the data where the next closest STE_DATE for Well(n) will not be Well(n-1).

 

ebygomm
Rank
Userlevel 1
Badge +21

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

ngstoke
Badge
The DateTimeCalculator in FME 2017.0+ can also be used effectively. If the date format in the source features was '%Y%m%d' originally, the DateFormatter (DateTimeConverter in FME 2017.1) would not be essential.

 

 

Thank you @takashi ! This is what I was trying to accomplish. I appreciate the detailed example.
takashi
Rank
Userlevel 2
Badge +17

Hi @ngstoke, if I understand your intention correctly, this workflow could work.

Note: I created this workflow with FME 20170. In the latest FME release version 2017.1, the DateFormatter has been upgraded to the DateTimeConverter.

0684Q00000ArLCBQA3.png

Is this your desired result?

0684Q00000ArL4SQAV.png

Good to hear my example helped you.

 

If you are familiar with SQL, the InlineQuerier could also be a good solution.

 

0684Q00000ArMSIQA3.png

InlineQuerier | SQL Query Example:

 

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 

 

takashi
Rank
Userlevel 2
Badge +17

Hi @ngstoke, if I understand your intention correctly, this workflow could work.

Note: I created this workflow with FME 20170. In the latest FME release version 2017.1, the DateFormatter has been upgraded to the DateTimeConverter.

0684Q00000ArLCBQA3.png

Is this your desired result?

0684Q00000ArL4SQAV.png

I'm interested in the capability of XQuery expressions to process features in conjunction with FME transformers. This is an experimental solution with XQuery.

 

0684Q00000ArMUrQAN.png

XMLTemplater | Template (XQuery expression):

 

<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>

 

Reply


Community Stats

28,681
Posts
109,402
Replies
36,540
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.