Intervalmatch and Join

[#1]

Hi all,

I have two tables (capacity offer and fact table) which I would like to join.

(see attached excel)

In the capacity offer table I have the planned capacity per workcenter, day and time interval.

In the fact table I have the activities and duration for each activity.

Now I would like to create two new fields in the fact table (capacity offer 1 and capacity offer 2) where it should be checked if capaity is planned (check with table "capacity offer") and if yes the capacity offer information should be calculated: TimestampEndFact minus TimestampStartFact to get the corresponding capacity offer for this event.

Additionally if there is an event in fact table where I do not find a match in capacity offer table, the capacity offer should be 0.

And I have also the case that I have events which which are related to two intervals. Here I would like that it should be allocated to the interval relating to the TimestampStartFact-field. (see line 54 in excel, where TimestampEndFact overlapps by 2 seconds to the next interval).

Technically I think it should work somehow with the intervalmatch funtion, but I have no clue how to connect and calculate the tables correctly.... Do anybody could help me here?

Thanks a lot for your feedback!

Best

John

[balar025]

Hi,

Might extended Interval match suffice your problem.

Please find attachment.

Regards,

Ravi Balar

[#3]

Hi Ravi,

for my example it works! Thanks!

But now I have trouble with data records where I have available capacity, but no facts.

For example if you delete first two data records in fact table, it will start  on 01.11.2016  01:06:48 AM.

From my capacity table I know that I have capacity also availabe for 12:00:00 AM to 01:06:47 AM.

Any idea how to solve this?