Create date segments of two table with overlap of times

Hi ​@kevinkies ,

If you consider each time duration as a line segment from (date start, 0) to (date end, 0), you can find overlapped segments with LineOnLineOverlayer easily.

Basic workflow would look like the screenshot below. Hovever, I'm not clear detailed requirement about how to keep which value of LEEGSTAND and other attributes from Table 1 or Table 2 in overlaped segments, how to sort resulting rows and so on, so you will have to customize the workflow according to your requirement.

You could do it spatially, convert dates to seconds since epoch, use that to create boxes, spatially intersect those with an areaonareaoverlayer, get the min and max coordinates which are the start and end dates of each segment in seconds and convert that back to a date

Hi ​@takashi , it works but indeed with no attribute so for. And i need them.

So i tried the solution of ​@ebygomm it works so far, but there is one thing that isnt good. That is if u use the table in the attachmenst. This is my outcome:

There are only 2  misstake in the table, BEDRIJFSNAAM is wrong its start one row to early or to late and WIJZIGING is ever row the samen. Further its perfect thanks!

Can u help me with that?

Can you explain the logic for how the attributes should be assigned? I couldn’t work it out

The first two rows (previous post) has the value in BEDRIJFSNAAM = Van der Vorm Vastgoed Holding B.V.

Only if u see in table_1 that the value of BEDRIJFSNAAM= Van der Vorm Vastgoed Holding B.V. ends on the date 20040119:

So bewteen 20040119 till 20160930 must be the value of BEDRIJFSNAAM = Van der Vorm Vastgoed B.V. like in table_1 is.

But in the outcome in my previous post is the value of BEDRIJFSNAAM=an der Vorm Vastgoed Holding B.V. ends on 20060716, and thats incorrect

But in Table2 there is record that goes beyond 20040119 which also has a value of Van der Vorm Vastgoed Holding B.V. ?

In the AreaOnAreaOverlayer, you can choose to drop the attributes in Accumulation Mode and then build a List with the attributes and then use some logic to choose which value of BEDRIFJSNAAM you want to keep (I’m afraid i still don’t understand how you arrive at this so can’t help there!)

Them u are right! I only want the use de BEDRIJFSNAAM of table_1:

So he must use the value betweens the date_start and date_end at the end. The BEDRIJFSNAAM in table_2 can be blank then.

Is that possible u think. If i make the values empty in table_2, then the outcome has no values filled.

​@kevinkies 

it works but indeed with no attribute so for. And i need them.

Overlapped segments will have two attribute sets from Table 1 and Table 2, and they all will be stored in a list attribute in my workflow example. I don't know which one should be kept finally, so the workflow is unfinished, as I told you that you will have to customize the workflow.

What are the rules for transferring the source two attribute sets to overlapped segments?

If you want the values from Table 1 to take precedence I think if you add an attribute to your input, e.g. sort and give a value of 1 for Table 1 and 2 for Table 2, you can then include this in the list you build in the area on area overlayer, sort the list on this value and then set BEDRIFJSNAAM like this

​@takashi the rules are as follows:

• date_start and date_end from bothe tables
• ID is key to connect both tables
• BEDRIJFSNAAM from table_1
• LEEGSTAND from table_2
• Eigdom_situatie from table_2
• WIJZIGING from both tables (but not important)

  Is this enough?

thanks ​@takashi it works fine. Only one comment left. I know i had write that the row WIJZIGING isnt very relevant. But since we are no so far, i realyy wont the have it perfect.

No the value of WIJZIGING is all the same name, but in reality it isnt. In 3 rows the value of WIJZIGING is EIGENAAR and the others are GEBRUIKER.

The rows who must have EIGENAAR are:

Is that also possible to fix?

I think it's possible, if you would clarify how you can determine which "WIJZIGING" value - from table 1 or table 2 should be assigned to the resulting segment.

In my observasion, it seems that the condition to adopt WIJZIGING from the table 1 in the resulting rows is, DATE_START in the resulting segment is equal the DATE_START from the table 1. If so, this workflow might help you.

​@takashi thanks. I add the same rules and also did it for table_2 the same way. So i have 3 possible values in de manager add WIJZIGING. Now he checked al possible start datum with the good value. 

I appreciate your help!

One question left :-)

Is it possible to copy the rows to a specific date, i mean as follows:

TO:

What i mean, now ik have date segment, and want to tranfer the segment to all days betweens the segment. The dots means the dates between.

Yes, you can do that with DateTimeCalculator x 2 + Cloner.

However, at first, you have to define how the DATE_END 99991231 should be treated. The number of days from 20190617 to 99991231 is approx. 2.9 millions, do you want to copy such a large number of rows?

99991231 is a value to de de math in the first steps. It is actually a null value, because that row actually has no date_end. The object is still in use. It is current day

Do you mean that the DATE_END 99991231 should be treated as the date of today - i.e. 2025-07-11 in my time zone when copying the rows?

Yes indeed!

Try adding these transformers to the previous workflow. Hope this helps!

​@takashi thanks, everything works

​@takashi there where 159 million records, myabe to big for oracle. Is it posiible to change it to only the first day of the month instead of every day?

Thanks already

Do you need to retain the date equal to the DATE_START for each segment?

Yes indeed. Now i see thats a problem if its nog begin on the first of the month probaly?

Try replacing the transformers from Sorter to DateTimeCalulator_2 in the previous workflow with these transformers. A bit complex, take a look at details carefully.